ConditionalSQL standard

COALESCE

Returns the first value in the list that isn't NULL.

Syntax

COALESCE(<value1>, <value2>, …)

Checks the arguments from left to right and returns the first one that isn't NULL. If they're all NULL, the result is NULL too. Most often it's used to substitute a default value, for example: “no phone number — show a dash”.

Data for the examples

employees

name	department	salary	bonus	phone
Emma	backend	1200	300	+44 7700 900123
Liam	backend	1000	NULL	NULL
Olivia	frontend	1350	150	+44 7700 900456
Noah	frontend	950	NULL	+44 7700 900789
Sophia	design	1200	200	NULL

These tables already exist in the sandbox — the examples below use them. Want to try this in your own database? Copy the SQL.

The first value that isn't NULL

SELECT COALESCE(NULL, NULL, 42) AS result;

result
42

A dash instead of an empty phone number

SELECT name, COALESCE(phone, '—') AS phone

FROM employees;

name	phone
Emma	+44 7700 900123
Liam	—
Olivia	+44 7700 900456
Noah	+44 7700 900789
Sophia	—

NULL “swallows” arithmetic

SELECT name,

salary + bonus AS without_coalesce,

salary + COALESCE(bonus, 0) AS total

FROM employees;

name	without_coalesce	total
Emma	1500	1500
Liam	NULL	1000
Olivia	1500	1500
Noah	NULL	950
Sophia	1400	1400

Any operation with NULL gives NULL: Liam and Noah have no bonus, so without COALESCE their total would vanish completely.

Watch out

The arguments must have compatible types. COALESCE(bonus, 'n/a') on a numeric bonus throws an error: PostgreSQL tries to turn 'n/a' into a number. Cast to text: COALESCE(bonus::text, 'n/a').

An empty string '' isn't NULL, so COALESCE won't replace it. For that, combine it with NULLIF: COALESCE(NULLIF(phone, ''), '—').

Related functions

NULLIF CASE

Learn more in the course

Handling NULL: COALESCE and NULLIF