TRY

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.

Tip

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

SQL

SELECT 'abc'::INTEGER;

db error: ERROR: Could not convert string 'abc' to INT32

With TRY

SQL

SELECT TRY('abc'::INTEGER);

 ?column?
----------
 NULL

Integer Overflow

Without TRY

SQL

SELECT 127::TINYINT + 1::TINYINT;

db error: ERROR: Overflow in addition of INT8 (127 + 1)!

With TRY

SQL

SELECT TRY(127::TINYINT + 1::TINYINT);

 ?column?
----------
 NULL

Casting Multiple Rows

Without TRY

SQL

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

db error: ERROR: Could not convert string 'test' to INT32 when casting from source column a

With TRY

SQL

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:

SQL

SELECT TRY(random())

db error: ERROR: TRY can not be used in combination with a volatile function