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.

Rationale

It's hard to wrap something like SQL Server's TRY_CAST into a generic PostgreSQL function. Input and output can be any data type, but SQL is strictly typed and Postgres functions demand that parameter and return types are declared at creation time.

Postgres has the concept of polymorphic types, but function declarations accept at most one polymorphic type. The manual:

Polymorphic arguments and results are tied to each other and are
resolved to a specific data type when a query calling a polymorphic
function is parsed. Each position (either argument or return value)
declared as anyelement is allowed to have any specific actual data
type, but in any given call they must all be the same actual type.

CAST ( expression AS type ) would seem like an exception to this rule, taking any type and returning any (other) type. But cast() only looks like a function while it's an SQL syntax element under the hood. The manual:

[...] When one of the two standard cast syntaxes is used to do a run-time
conversion, it will internally invoke a registered function to perform
the conversion.

There is a separate function for each combination of input and output type. (You can create your own with CREATE CAST ...)
Function

My compromise is to use text as input since any type can be cast to text. The extra cast to text means extra cost (though not much). Polymorphism also adds a bit of overhead. But the moderately expensive parts are the dynamic SQL we need, the involved string concatenation and, most of all, exception handling.

That said, this little function can be used for any combination of types including array types. (But type modifiers like in varchar(20) are lost):

CREATE OR REPLACE FUNCTION try_cast(_in text, INOUT _out ANYELEMENT)
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format('SELECT %L::%s', $1, pg_typeof(_out))
   INTO  _out;
EXCEPTION WHEN others THEN
   -- do nothing: _out already carries default
END
$func$;

The INOUT parameter _out serves two purposes:

• declares the polymorphic type

• also carries the default value for error cases

You wouldn't call it like in your example:

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

.. where COALESCE also eliminates genuine NULL values from the source (!!), probably not as intended. But simply:

SELECT try_cast(data, 0);

.. which returns NULL on NULL input, or 0 on invalid input.

The short syntax works while data is a character type (like text or varchar) and because 0 is a numeric literal that is implicitly typed as integer. In other cases, you may have to be more explicit:
Example calls

Untyped string literals work out of the box:

SELECT try_cast('foo', NULL::varchar);
SELECT try_cast('2018-01-41', NULL::date);   -- returns NULL
SELECT try_cast('2018-01-41', CURRENT_DATE); -- returns current date

Typed values that have a registered implicit cast to text work out of the box, too:

SELECT try_cast(name 'foobar', 'foo'::varchar);
SELECT try_cast(my_varchar_column, NULL::numeric);

Comprehensive list of data types with registered implicit cast to text:

SELECT castsource::regtype
FROM   pg_cast
WHERE  casttarget = 'text'::regtype
AND    castcontext = 'i';

All other input types require an explicit cast to text:

SELECT try_cast((inet '192.168.100.128/20')::text, NULL::cidr);
SELECT try_cast(my_text_array_column::text, NULL::int[]));

We could easily make the function body work for any type, but function type resolution fails. Related:

• How to avoid implicit type casts in PostgreSQL?

StackExchange Database Administrators Q#203934, answer score: 33