Extracting more useful information from PSQLException

Hi everyone,

While designing web services that are backed by PostgreSQL, it is often helpful to report a more useful HTTP error than a generic "Something went wrong, sorry dude".

The thrown PSQLException has a SQLState variable which can tell you the type of failure that occurred, but there is much more useful information in the exception message that does not seem to be available in a structured manner. For example, if a UNIQUE constraint or a CHECK constraint is violated, the constraint name is in the error message. This could be useful as it is possible that the violation of a "email UNIQUE" constraint is most appropriate as a HTTP 400, since the user provided an email that is already in use, while a "id PRIMARY KEY" constraint is most appropriately a HTTP 500 since the server clearly shouldn't be inserting duplicate IDs and this is an internal error.

Has anyone found a satisfying solution to this problem? My current approach is to parse the exception message with a regex to get the interesting bits, but this is not robust to database message changes in newer versions of PG, locale / translations, etc…

Is it possible / would it be a good addition to expose structured SQL error information through JDBC? If such a thing does not exist, is this a feasible contribution? I expect it might require some hacking on the PostgreSQL server side to expose the error information in a structured manner. Or am I just dreaming too much, and this is not feasible?

Thanks,
Steven Schlansker