PostgreSQL COALESCE

Syntax

COALESCE(value1, value2, ..., valueN)

COALESCE takes any number of arguments and returns the first one that is not NULL. If all arguments are NULL, it returns NULL.

It short-circuits: as soon as it finds a non-NULL value, it stops evaluating the remaining arguments.

Basic example

SELECT COALESCE(NULL, NULL, 'default');
-- returns: 'default'

SELECT COALESCE('first', 'second', 'third');
-- returns: 'first'

SELECT COALESCE(NULL, NULL, NULL);
-- returns: NULL

Substituting NULL columns

A common use case: a users table where display_name is optional, and you want to fall back to username when it's missing.

SELECT
  id,
  COALESCE(display_name, username) AS name
FROM users;

Rows 2 and 4 have no display_name, so COALESCE returns their username instead.

COALESCE with aggregates

SUM and other aggregate functions return NULL when no rows match a condition. Wrapping the aggregate in COALESCE converts that NULL to a more useful default.

SELECT
  product_id,
  COALESCE(SUM(quantity), 0) AS total_sold
FROM order_items
GROUP BY product_id;

This matters most when using LEFT JOIN to include products that have never been ordered:

SELECT
  p.id,
  p.name,
  COALESCE(SUM(oi.quantity), 0) AS total_sold
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.id
GROUP BY p.id, p.name;

Widget C has no orders, so without COALESCE its total_sold would be NULL.

Multiple fallback chain

You can chain as many fallbacks as you need. The first non-NULL value wins.

SELECT
  id,
  COALESCE(nickname, first_name, 'Anonymous') AS display_name
FROM users;

Row 1 uses the nickname. Row 2 falls through to first_name. Row 3 falls through to 'Anonymous'. Row 4 uses the nickname even though first_name is NULL.

COALESCE in WHERE clauses

COALESCE works anywhere an expression is valid, including WHERE clauses. This is useful when filtering on a column that may be NULL.

-- Find users whose display name or username contains 'al'
SELECT *
FROM users
WHERE COALESCE(display_name, username) ILIKE '%al%';

Another pattern: treat NULL as a specific default when filtering.

-- Treat NULL priority as 0, then filter
SELECT *
FROM tasks
WHERE COALESCE(priority, 0) >= 2;

Without COALESCE, rows where priority IS NULL would not be returned, even if you intend to treat them as priority 0.

COALESCE vs NULLIF

NULLIF does the opposite of COALESCE: it returns NULL if two values are equal, otherwise it returns the first value.

NULLIF(value, compare_value)

A practical use: treating empty strings as NULL so that COALESCE can replace them.

SELECT COALESCE(NULLIF(status, ''), 'unknown') AS status
FROM orders;

If status is '', NULLIF converts it to NULL, and then COALESCE replaces that NULL with 'unknown'. If status is already NULL or any other non-empty value, it passes through NULLIF unchanged and COALESCE does its normal job.

COALESCE vs CASE

COALESCE(a, b) is shorthand for a specific CASE pattern:

-- These two are equivalent:
COALESCE(display_name, username)

CASE
  WHEN display_name IS NOT NULL THEN display_name
  ELSE username
END

Use COALESCE when your condition is purely "is this NULL". Use CASE when you need more complex logic, such as falling back on a different value based on a non-NULL check:

-- CASE handles conditions COALESCE cannot
CASE
  WHEN status = 'cancelled' THEN 'n/a'
  WHEN price IS NULL THEN 0
  ELSE price
END

COALESCE is shorter and more readable for the common NULL-substitution pattern. If you find yourself writing a CASE that only checks IS NOT NULL, replace it with COALESCE.

Performance note

COALESCE short-circuits and stops evaluating arguments once it finds a non-NULL value. In practice, this means the later arguments are not evaluated if an earlier one is non-NULL.

However, be cautious when passing subqueries as arguments:

-- Potentially wasteful: the subquery may run even when not needed
SELECT COALESCE(
  fast_column,
  (SELECT expensive_computation FROM big_table WHERE ...)
)
FROM my_table;

PostgreSQL's planner does not always guarantee lazy evaluation of COALESCE arguments, particularly with subqueries in complex plans. If you have an expensive fallback that is rarely needed, consider restructuring the query with a CASE expression or handling the fallback in application code.

For simple column references and literals, there is no meaningful performance difference between COALESCE and the equivalent CASE.

Quick reference

| Expression | Result | |------------|--------| | COALESCE(NULL, 'b') | 'b' | | COALESCE('a', 'b') | 'a' | | COALESCE(NULL, NULL) | NULL | | COALESCE(col, 0) | col if not NULL, else 0 | | COALESCE(SUM(col), 0) | 0 when no rows match | | COALESCE(a, b, c) | First non-NULL of a, b, c | | COALESCE(NULLIF(s, ''), 'default') | Treats empty string as NULL | | NULLIF(a, b) | NULL if a = b, else a |