PostgreSQL alternative to SQL Server’s `try_cast` function Microsoft SQL Server has what I consider a remarkably sensible function, try_cast() which returns a null if the cast is unsuccessful, rather than raising an error.

This makes it possible to then use a CASE expression or a coalesce to fall back on. For example:

SELECT coalesce(try_cast(data as int),0);


The question is, does PostgreSQL have something similar?

The question is asked to fill in some gaps in my knowledge, but there’s also the general principle that some prefer a less dramatic reaction to some user errors. Returning a null is more easily taken in one's stride in SQL than an error. For example SELECT * FROM data WHERE try_cast(value) IS NOT NULL;. In my experience, user errors are sometimes better handled if there is a plan B.
If casting from one specific type to one other specific type is enough, you can do this with a PL/pgSQL function:

create function try_cast_int(p_in text, p_default int default null)
   returns int
as
$$
begin
  begin
    return $1::int;
  exception 
    when others then
       return p_default;
  end;
end;
$$
language plpgsql;


Then

select try_cast_int('42'), try_cast_int('foo', -1), try_cast_int('bar')


Returns

try_cast_int | try_cast_int | try_cast_int
-------------+--------------+-------------
          42 |           -1 |             


If this is only for numbers, another approach would be to use a regular expression to check if the input string is a valid number. That would probably  be faster than catching exceptions when you expect many incorrect values.