Hi everyone,
While designing web services that are backed by PostgreSQL, it is often helpful to report a more useful HTTP error than
ageneric "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
muchmore 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
couldbe useful as it is possible that the violation of a "email UNIQUE" constraint is most appropriate as a HTTP 400,
sincethe user provided an email that is already in use, while a "id PRIMARY KEY" constraint is most appropriately a
HTTP500 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
regexto 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
doesnot exist, is this a feasible contribution? I expect it might require some hacking on the PostgreSQL server side
toexpose the error information in a structured manner. Or am I just dreaming too much, and this is not feasible?
Thanks,
Steven Schlansker
