COALESCE in PostgreSQL

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 and prevent NULL propagation in calculations.

Syntax

COALESCE(argument_1, argument_2, ...)

Quick scalar examples:

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

Practical Example

A product catalog where some items have a promotional discount. Without COALESCE, arithmetic on a NULL discount produces a NULL net price:

CREATE TABLE products (
  id            SERIAL PRIMARY KEY,
  name          VARCHAR(100) NOT NULL,
  list_price    NUMERIC(10, 2) NOT NULL,
  discount      NUMERIC(10, 2)   -- nullable: NULL means no discount
);

INSERT INTO products (name, list_price, discount) VALUES
  ('Bolt Pack',      12.00, 2.00),
  ('Hex Wrench Set', 45.00, 5.00),
  ('Safety Goggles', 18.00, NULL),
  ('Work Gloves',     8.50, NULL);

-- Without COALESCE: rows with NULL discount show NULL net_price
SELECT
  name,
  list_price,
  discount,
  list_price - discount AS net_price
FROM products;

       name       | list_price | discount | net_price
------------------+------------+----------+-----------
 Bolt Pack        |      12.00 |     2.00 |     10.00
 Hex Wrench Set   |      45.00 |     5.00 |     40.00
 Safety Goggles   |      18.00 |     NULL |      NULL
 Work Gloves      |       8.50 |     NULL |      NULL

Use COALESCE to treat NULL discount as zero:

SELECT
  name,
  list_price,
  COALESCE(discount, 0)            AS effective_discount,
  list_price - COALESCE(discount, 0) AS net_price
FROM products;

       name       | list_price | effective_discount | net_price
------------------+------------+--------------------+-----------
 Bolt Pack        |      12.00 |               2.00 |     10.00
 Hex Wrench Set   |      45.00 |               5.00 |     40.00
 Safety Goggles   |      18.00 |               0.00 |     18.00
 Work Gloves      |       8.50 |               0.00 |      8.50

Multi-Level Fallbacks and Combining with NULLIF

Arguments can be arbitrary expressions, not just constants. Chain multiple fallback sources:

CREATE TABLE events (
  id           SERIAL PRIMARY KEY,
  title        VARCHAR(200) NOT NULL,
  short_desc   TEXT,      -- preferred display text, may be null
  full_desc    TEXT,      -- longer fallback, may be null
  auto_summary TEXT       -- machine-generated fallback
);

SELECT
  id,
  title,
  COALESCE(short_desc, LEFT(full_desc, 160), auto_summary, 'No description available') AS display_text
FROM events;

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

-- NULLIF(short_desc, '') converts '' to NULL so COALESCE can skip it
SELECT
  id,
  title,
  COALESCE(NULLIF(short_desc, ''), LEFT(full_desc, 160), 'No description') AS display_text
FROM events;

Verify NULL handling in pg_stats after an ANALYZE run:

SELECT attname, null_frac, n_distinct
FROM pg_stats
WHERE tablename = 'products' AND attname = 'discount';

Testing with Vela

Null-handling logic is sensitive to real data distributions. Create a Vela branch from your production snapshot to test COALESCE chains against actual NULL proportions in each column. Running the query on a branch lets you verify that edge cases—all-NULL rows, mixed-type fallbacks—behave correctly before deploying the change.

Production Tips

• COALESCE is the SQL-standard equivalent of IFNULL (MySQL) and NVL (Oracle). Prefer it for cross-database portability.
• Avoid wrapping indexed columns in COALESCE inside WHERE clauses. WHERE COALESCE(col, 0) = 0 disables index use; rewrite as WHERE col IS NULL OR col = 0.
• All arguments must resolve to compatible types; PostgreSQL raises an error if types cannot be unified. Use explicit casts when combining columns of different numeric types.
• Arguments after the first non-NULL value are never evaluated—this is safe for expensive subqueries but can mask errors in later arguments if not tested separately.
• For nullable columns that frequently need a default in SELECT output, consider a generated column or a view with COALESCE baked in to keep application queries simpler.