⌘+k ctrl+k
1.3 (stable)
Search Shortcut cmd + k | ctrl + k
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 as 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 or with a scalar subquery. For example:

SELECT TRY(random())

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