How to determine PostgreSQL Error Codes from error message

Question

In a PL/pgSQL function, I want to trap an error. For example, when I convert text to a number, I get an error (I run it in psql, but get the same error in the Postico GUI client):

select 'a'::numeric;

ERROR:  invalid input syntax for type numeric: "a"
LINE 1: select 'a'::numeric;
               ^

To trap this error, I made an EXCEPTION-clause like this:

CREATE OR REPLACE FUNCTION public.to_number(input text) RETURNS numeric
    AS $$
BEGIN
    RETURN input::numeric;
EXCEPTION
    WHEN OTHERS THEN 
        RETURN NULL;
END
$$
    LANGUAGE plpgsql
    IMMUTABLE
    RETURNS NULL ON NULL INPUT
;

However, I don't like the condition WHEN OTHERS.

How do I map the error message ERROR: invalid input syntax for type numeric: "a" to one mentioned in the Appendix A. PostgreSQL Error Codes?

I want to catch the conversion error when converting to numeric and no other conditions (as the above function is a simplified one).

I have the feeling I'm missing something, but what?

Laurenz Albe · Accepted Answer · 2021-11-23 09:54:56Z

5

When you get the error in psql, run

\errverbose

and you'll get information like

ERROR:  22P02: invalid input syntax for type numeric: "a"
LINE 1: select 'a'::numeric;
               ^
LOCATION:  set_var_from_str, numeric.c:6856

22P02 is the SQLSTATE, and Appendix A of the documentation will tell you that that is invalid_text_representation.

answered Nov 23, 2021 at 9:54

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to determine PostgreSQL Error Codes from error message

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related