[FIXED] How can I recognize a specific Postgres CONSTRAINT

Issue

We used to have a constraint in the (Java) domain logic that prevented an entity (foo,task_id) of being inserted into table entity_a if a that task_id was already present. We then threw a specific exception that could be listened for.

Now we moved that logic into the database where it should be:

ALTER TABLE report ADD CONSTRAINT task_id_unique UNIQUE (task_id);

Now I still want to throw the same exception as before, but I need to recognize the specific exception case thrown by the database layer to do this. Is there a somewhat stable way I can recognize the named constraint? It should preferably not depend on the language set on the server, as it could be different and I also know error messages are different from one Postgres version to another.

We are specifically using Jdbi which is very close to the underlying JDBC level, so getting any details should be fine.

Solution

You can cast the java.sql.SQLException to an org.postgresql.util.PSQLException. Then you can call the method getServerErrorMessage() to get an org.postgresql.util.ServerErrorMessage. Then call the method getConstraint() to get the name of the constraint. Check the SQLSTATE to make sure it was really a constraint violation.

Answered By - Laurenz Albe

Answer Checked By - David Goodson (PHPFixing Volunteer)