TRY Expression

The TRY expression ensures that errors caused by the input rows in the child (scalar) expression result in NULL for those rows, instead of causing the query to throw an error.

The TRY expression was inspired by the TRY_CAST expression.

Examples

The following calls return errors when invoked without the TRY expression. When they are wrapped into a TRY expression, they return NULL:

Casting

Without TRY

SELECT 'abc'::INTEGER;

Conversion Error:
Could not convert string 'abc' to INT32

With TRY

SELECT TRY('abc'::INTEGER);

NULL

Logarithm on Zero

Without TRY

SELECT ln(0);

Out of Range Error:
cannot take logarithm of zero

With TRY

SELECT TRY(ln(0));

NULL

Casting Multiple Rows

Without TRY

WITH cte AS (FROM (VALUES ('123'), ('test'), ('235')) t(a))
SELECT a::INTEGER AS x FROM cte;

Conversion Error:
Could not convert string 'test' to INT32

With TRY

WITH cte AS (FROM (VALUES ('123'), ('test'), ('235')) t(a))
SELECT TRY(a::INTEGER) AS x FROM cte;

x
123
NULL
235

Limitations

TRY cannot be used in combination with a volatile function, an aggregate function, or a scalar subquery. For example:

SELECT TRY(random())

Binder Error:
TRY can not be used in combination with a volatile function