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