PostgreSQL alternative to SQL Server’s `try_cast` function
31
votes
7
answers
51070
views
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.
Asked by Manngo
(3145 rep)
Apr 14, 2018, 06:20 AM
Last activity: Aug 6, 2025, 04:15 PM
Last activity: Aug 6, 2025, 04:15 PM