COALESCE
Return the first non-NULL argument, evaluated left to right. Standard SQL, supported everywhere, takes any number of arguments. The canonical tool for fallback chains and for substituting a default before feeding a value to arithmetic, formatting, or comparison.
Syntax #
SELECT COALESCE(col_1, col_2, col_3, fallback)
FROM table_name;Example #
All COALESCE arguments must have a compatible type. Passing a mix of strings, numbers, and dates triggers an error or forces an implicit cast — which quietly changes the output (e.g. 123 → '123'). When fallback values are literals, type them to match: COALESCE(numeric_col, 0::NUMERIC) or COALESCE(date_col, DATE '1970-01-01'). And COALESCE is short-circuit: the later arguments aren't evaluated if an earlier one is non-null, which matters when one of them is an expensive subquery or function call.