sqlstate and sqlerrm in PostgreSQL

what is sqlstate and sqlerrm ?

All messages emitted by the PostgreSQL server are assigned five-character error codes that follow the SQL standard’s conventions for “SQLSTATE” codes. Applications that need to know which error condition has occurred should usually test the error code, rather than looking at the textual error message. The error codes are less likely to change across PostgreSQL releases, and also are not subject to change due to localization of error messages. Note that some, but not all, of the error codes produced by PostgreSQL are defined by the SQL standard; some additional error codes for conditions not defined by the standard have been invented or borrowed from other databases.

how we can access sqlstate and sqlerrm ?

SQLSTATE and SQLERRM can be accessed within an exception handler. Exception handlers frequently need to identify the specific error that occurred.

The special variable SQLSTATE contains the error code that corresponds to the exception that was raised (refer to Table for a list of possible error codes). The special variable SQLERRM contains the error message associated with the exception. These variables are undefined outside exception handlers.

skeleton function with sqlstate and sqlerrm

here is the example of using sqlstate and sqlerrm :

AS $function$


    --some query

      WHEN others THEN
        return SQLSTATE;
      USING ERRCODE = sqlstate
           ,MESSAGE = 'sqlstate = ' || sqlstate || '/' || sqlerrm;

LANGUAGE plpgsql;

these two variables are very handy, especially when you want to redirect the error message into a log file or just simply print it in standard output. Always remember these two variable only available inside exception part while in a transaction.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>