COALESCE

Returns the value of the first non-null expression in the list.

Returns the value of the first non-null expression in the list. If all expressions evaluate to null, then COALESCE returns null.

COALESCE conforms to the ANSI SQL-92 standard.

Behavior type

Immutable

Syntax

COALESCE ( { * | expression[,...] } )

Arguments

* | expression[,...]
One of the following:

  • * (asterisk)

    Evaluates all columns in the queried table.

  • expression

    An expression of any data type. Functions that are included in expression must be deterministic.

Examples

COALESCE returns the first non-null value in each row that is queried from table lead_vocalists. Note that in the first row, COALESCE returns an empty string.

=> SELECT quote_nullable(fname)fname, quote_nullable(lname)lname,
     quote_nullable(coalesce (fname, lname)) "1st non-null value" FROM lead_vocalists ORDER BY fname;
  fname  |   lname   | 1st non-null value
---------+-----------+--------------------
 ''      | 'Sting'   | ''
 'Diana' | 'Ross'    | 'Diana'
 'Grace' | 'Slick'   | 'Grace'
 'Mick'  | 'Jagger'  | 'Mick'
 'Steve' | 'Winwood' | 'Steve'
 NULL    | 'Cher'    | 'Cher'
(6 rows)

See also