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

Query

SELECT 'abc'::INTEGER;

Result

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

With TRY

Query

SELECT TRY('abc'::INTEGER);

Result

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

Integer Overflow

Without TRY

Query

SELECT 127::TINYINT + 1::TINYINT;

Result

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

With TRY

Query

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

Result

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

Casting Multiple Rows

Without TRY

Query

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

Result

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

With TRY

Query

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

Result

 x------  123 NULL  235

Limitations

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

Query

SELECT TRY(random())

Result

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