COALESCE

Evaluates and returns the first non-null expression, or NULL if all expressions are NULL.

Syntax

COALESCE( expr, [, ...] )

Arguments

Parameter Description
expr1, expr2, … Expressions of the same type

Returns

Returns the first non-null argument or NULL if all expressions are null.

Notes

  • All expressions must have the same type, which is also the type of the result.
  • See also ISNULL. ISNULL(x,y) is equivalent to COALESCE(x,y).

Examples

Coalesce

master=> SELECT COALESCE(NULL, NULL, NULL, 5);
5

Replacing NULL values in aggregations

In some cases, replacing NULL values with a default can affect results.

master=> SELECT
.>               AVG(num_eyes),
.>               AVG(COALESCE(num_eyes,2)) AS "Corrected average"
.>
.>       FROM (
.>             VALUES ('Copepod',1),('Spider',8)
.>                   ,('Starfish',5),('Praying mantis',5)
.>                   ,('Human (average)',2),('Eagle',2)
.>                   ,('Horseshoe crab',10),('Kiwi',NULL)
.>                   ,('Fox',NULL),('Badger',NULL)
.>             ) AS cool_animals (name , num_eyes);
avg | Corrected average
----+------------------
  4 |                 3