PostgreSQL alternative to SQL Server’s `try_cast` function
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.
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) 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$ LANGUAGE plpgsql;
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?