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 :

CREATE OR REPLACE FUNCTION func_name(...)
 RETURNS text
AS $function$
  
  declare
    --foo=bar

  begin

    --some query

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

  end;
$function$ 
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.