Conditional functions

coalesce#

coalesce(value [, ...]) - returns the first non-null argument in a provided list of arguments in cases where null values should not appear in query results.

This function is an implementation of the COALESCE expression in PostgreSQL and as such, should follow the expected behavior described in the coalesce PostgreSQL documentation

Arguments:

  • coalesce(value [, ...]) value and subsequent comma-separated list of arguments which may be of any type except binary. If the provided arguments are of different types, one should be CASTable to another.

Return value:

The return value is the first non-null argument passed

Examples:

Given a table with the following records:

timestampamount
2021-02-11T09:39:16.332822Z1
2021-02-11T09:39:16.333481Znull
2021-02-11T09:39:16.333511Z3

The following example demonstrates how to use coalesce() to return a default value of 0 for an expression if the amount column contains null values.

SELECT timestamp,
coalesce(amount, 0) as amount_not_null
FROM transactions
timestampamount_not_null
2021-02-11T09:39:16.332822Z1
2021-02-11T09:39:16.333481Z0
2021-02-11T09:39:16.333511Z3