COALESCE

The COALESCE() function accepts any number of arguments and returns the first one that is not null. It evaluates arguments left to right and stops as soon as it finds a non-null value—remaining arguments are not evaluated. This makes it the idiomatic way to provide fallback values for nullable columns.

COALESCE syntax and basic examples

COALESCE(argument_1, argument_2, ...);

Simple scalar examples:

SELECT COALESCE(1, 2);       -- returns 1 (first non-null)
SELECT COALESCE(NULL, 2, 1); -- returns 2 (skips null, returns next non-null)

Output of the second query:

 coalesce
----------
        2

Substituting defaults in table queries

A classic use case: calculate net price when the discount column can be null.

CREATE TABLE items (
  id       SERIAL PRIMARY KEY,
  product  VARCHAR(100) NOT NULL,
  price    NUMERIC NOT NULL,
  discount NUMERIC       -- nullable
);

INSERT INTO items (product, price, discount)
VALUES ('A', 1000, 10), ('B', 1500, 20), ('C', 800, 5), ('D', 500, NULL);

SELECT
  product,
  price - COALESCE(discount, 0) AS net_price
FROM items;

Output:

 product | net_price
---------+-----------
 A       |       990
 B       |      1480
 C       |       795
 D       |       500  -- discount treated as 0 instead of NULL

Without COALESCE, any arithmetic involving NULL propagates as NULL—product D's net price would show as null.

COALESCE with expressions as fallbacks

COALESCE arguments can be arbitrary expressions, not just constants. For example, fall back to the first 150 characters of body text when an excerpt is missing:

SELECT
  title,
  COALESCE(excerpt, LEFT(body, 150)) AS display_excerpt
FROM posts;

Combine with NULLIF to treat empty strings the same as null:

SELECT
  title,
  COALESCE(NULLIF(excerpt, ''), LEFT(body, 40)) AS display_excerpt
FROM posts;

Practical tips

• COALESCE is the SQL-standard equivalent of IFNULL (MySQL) and NVL (Oracle). Prefer it for portability.
• Arguments after the first non-null value are never evaluated—this matters if later arguments have side effects or are expensive subqueries.
• All arguments must be of compatible types; PostgreSQL will raise an error if the types cannot be unified.
• Using COALESCE in a WHERE clause can prevent index scans. Consider rewriting the condition as col IS NULL OR col = default when performance is critical.