Re: Range for user-defined SQLSTATE codes

Ian Pilcher <arequipeno(at)gmail(dot)com> wrote:

> I can't be the first person (or even the 10,000th) to want to define my
> own SQLSTATE codes when raising errors in a stored procedure.  I've
> just tested doing so in a PL/pgSQL function access via JDBC, and I had
> no problem retrieving the non-standard state from the SQLException.  (I
> used 'WWWWW' and 'ZZZZZ' as my tests.)
>
> Is there anything like a standard range -- formal or otherwise -- for
> such codes?  A best practice?  A general consensus?  A half-painted
> bike shed?

There is this in the SQL standard.  According to that, SQLSTATE
values with 0-4 or A-H in both the first and third positions are
reserved for values defined by standards.  All others are available
for "implementation-specified" exception conditions.  As far as I
know, the PostgreSQL community has claimed SQLSTATE values with P0
or XX in the start of a SQLSTATE or P in the third character.  To
allow for future expansion by the PostgreSQL community it might be
wise to stay away from any SQLSTATE starting with P for your
application use.

-Kevin