Search code examples
cpostgresqllibpq

PostgreSQL: How can my C (libpq) program detect which table constraint triggered error?

I am trying to programmatically migrate a large dataset from an old MySQL database to PostgreSQL. The source data is a mess, so my pgsql tables have a bunch of constraints to catch bad data going in.

When an insert fails because of one of these constraints, how does my C program discover which constraint prevented the insert?

For example, one of the simpler tables looks like:

create table glue (
    product_id int not null references product(id),
    history_id int not null references history(id),
    constraint glue_idx unique (product_id,history_id)
);

Now, my program ends up executing an insert that triggers one of the constraints, PQresultStatus only tells me PGRES_FATAL_ERROR, and PQerrorMessage tells me:

ERROR:  duplicate key value violates unique constraint "glue_idx"
DETAIL:  Key (product_id, history_id)=(413, 1762) already exists.

which is perfectly readable to me, a human. My question is, how can my C program discern that the error was caught by the glue_idx constraint?

Solution

  • Use the function

    char *PQresultErrorField(const PGresult *res, int fieldcode)

    which returns detailed information depending on fieldcode. One of possible codes is PG_DIAG_CONSTRAINT_NAME.

    Find the function description in the documentation.