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?
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.
