COALESCE

All functions > CONDITIONAL > COALESCE

Returns the first non-NULL value from a list of values

Syntax

Diagram(
  Sequence(
    Terminal("COALESCE"),
    Terminal("("),
    NonTerminal("value"),
    Terminal(","),
    OneOrMore(NonTerminal("value"), Terminal(",")),
    Terminal(")"),
  )
)

Notes

• Evaluates values in order from left to right
• Returns immediately upon finding the first non-NULL value
• Returns NULL only if all values are NULL
• All values must be of the same or compatible types
• Commonly used to provide default values for NULL columns

Examples

Basic usage

SELECT
    f1 := COALESCE(1, 2, 3),  -- First value is non-NULL
    f2 := COALESCE(NULL::BIGINT, 2, 3),  -- Skip NULL, return second value
    f3 := COALESCE(NULL::BIGINT, NULL::BIGINT, 42)  -- Skip multiple NULLs
;

Other types

SELECT
    f1 := COALESCE(NULL::VARCHAR, 'default')  -- Provide default for NULL string
;