COALESCE(...)

All functions > CONDITIONAL > COALESCE(...)

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

Syntax

COALESCE(value, value, ...)

Notes

  • COALESCE is the variadic form you write in FeatureQL (same idea as SQL). Arguments are evaluated left to right; the first non-NULL value wins.
  • If every argument is NULL, the result is NULL (use typed NULLs such as NULL::BIGINT in argument lists so types are known).
  • All arguments must share a common or compatible type.
  • Often used to substitute defaults for nullable columns or expressions.

Examples

Basic usage

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

Other types

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

More patterns

FeatureQL
SELECT
    -- First non-NULL VARCHAR wins
    f1 := COALESCE('x', 'y'),
    -- All NULL → NULL
    f2 := COALESCE(NULL::BIGINT, NULL::BIGINT),
    -- NULL boolean skipped
    f3 := COALESCE(NULL::BOOLEAN, TRUE),
    -- Skip two NULLs
    f4 := COALESCE(NULL::VARCHAR, NULL::VARCHAR, 'fallback')
;
Result
f1 VARCHARf2 BIGINTf3 BOOLEANf4 VARCHAR
xNULLtruefallback

Suggest changes to this page
Last update at: 2026/06/20 10:08:10