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
    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
;
Result
f1 BIGINTf2 BIGINTf3 BIGINT
1242

Other types

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

More patterns

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

Suggest changes to this page
Last update at: 2026/05/26 17:22:09