How to handle type conversion error in Postgresql query?

Question

I was facing an error on type conversion in the Oracle server then I found one of the solutions mentioned below:

SELECT TO_NUMBER('b' default -1 on conversion error) from dual;

output: -1

As mentioned in the above query, it will give the default value if the value is non-numeric.

Is there any way to handle the same thing in the Postgres SQL?

You will need to write a custom PL/pgSQL function that traps the error. There is nothing built-in in Postgres. — , Oct 14 '22 at 11:17
Answer in this link will help you https://stackoverflow.com/a/2894527/5589975 — Vaibhav Parab, Oct 14 '22 at 11:33

Zegarek · Answer 1 · 2022-10-14T12:17:58.507

As per @a_horse_with_no_name's comment: PostgreSQL doesn't offer a built-in function like that, but it's pretty simple to make one in PL/pgSQL yourself.

CREATE OR replace FUNCTION to_number(
    argument text,
    default_result integer default -1)
RETURNS integer LANGUAGE plpgsql
AS $$
BEGIN
    RETURN argument::integer;
EXCEPTION WHEN OTHERS THEN
    RETURN default_result;
END $$;

or, even in plain SQL - as suggested by Vaibhav Parab's comment

CREATE OR replace FUNCTION to_number(argument text,default_result integer default -1)
RETURNS integer LANGUAGE SQL 
AS '
SELECT CASE 
          WHEN argument~E''^\\d+$'' 
            THEN argument::integer 
          ELSE default_result 
       END';

Now you can call either by select to_number('b', default_result=>-1);. The language sql function variant does not impose any additional overhead compared to typing out the entire case directly because it meets all the inlining criteria so it performs the same while being shorter.

Here's a working DB<>fiddle.

How to handle type conversion error in Postgresql query?

1 Answers1