PHP Postgres constraint violation - can I get the constraint name somehow?

Question

We have a web application talking to a Postgres SQL database at work - I've set up many constraints on the server to keep data consistent but we have problems with reporting nicely what it is that prevents the user from entering his (invalid) data at a given moment.

The only thing we can get is "Constraint violation" but that isn't very descriptive, from perl or directly from PGAdmin we get nice info as to which constraint caused the failure (the constraint's name) - is there a way to do the same in PHP?

We're able to upgrade PHP or use a different db access module if it would help so I'm interested in knowing if it's possible to do using any way that you could safely recommend.

Answer 1

Using pg_last_error:

$c = pg_connect('service=example');
$q = 'INSERT INTO example VALUES ($1, $2)';

$s = pg_query_params($c, $q, array('XXX',2.2222));
echo pg_last_error($c);

I get:

ERROR:  duplicate key value violates unique constraint "example_example_key"

"example_example_key" being the name of the constraint in question.

Answer 2

You'd better use pg_result_error_field, this function gives you much more detailed information about the error. If you use stored procedures and let these throw exceptions including DETAIL's and HINT's, errorhandling in your code will be really easy.