PostgreSQL COALESCE Function

Need one phone number per customer when some columns are empty? The PostgreSQL COALESCE expression returns the first argument that isn’t NULL: COALESCE(mobile_phone, work_phone, 'no phone').

The catch: an empty string isn’t NULL in PostgreSQL. In my run, a blank mobile beat the work number.

Five examples follow, ending with arguments COALESCE never evaluates. Tested on PostgreSQL 18.6 on Windows.

COALESCE Function Syntax

Here is the syntax:

COALESCE(value [, ...])
ArgumentRequiredWhat it means
valueYes, one or moreAny expression, checked left to right

Return value: the first non-NULL argument, in their common type, or NULL if all are NULL.

Availability: all supported releases, including 18. The docs call it a conditional expression, not an ordinary function (no VARIADIC arrays). It’s shorthand for a PostgreSQL CASE expression.

When to Use the COALESCE Function

Use COALESCE when you need to:

  • show 0 or a label instead of blanks
  • pick the first phone that exists
  • turn a NULL outer-join total into 0
  • backfill NULLs in a PostgreSQL UPDATE

Run this once; every example uses it:

CREATE TABLE customers (
  customer_id  int PRIMARY KEY,
  name         varchar(20),
  mobile_phone varchar(12),
  work_phone   varchar(12),
  home_phone   varchar(12)
);
INSERT INTO customers VALUES
 (1, 'Hannah Brooks',  '614-555-0142', NULL, NULL),
 (2, 'Tyler Reed',     NULL, '512-555-0177', NULL),
 (3, 'Grace Miller',   NULL, NULL, '305-555-0119'),
 (4, 'Marcus Bennett', NULL, NULL, NULL),
 (5, 'Olivia Carter',  '',   '206-555-0163', NULL);
CREATE TABLE orders (
  order_id    int PRIMARY KEY,
  customer_id int,
  total       numeric(8,2),
  discount    numeric(6,2)
);
INSERT INTO orders VALUES
 (1001, 1, 129.99, 10.00),
 (1002, 1,  64.50, NULL),
 (1003, 2, 310.00, 25.00);

Example 1: Replace NULL with a Default Value

First, the most common job. Order 1002 has no discount.

I want each net amount, counting a missing discount as 0. Here is the query:

SELECT order_id,
       total - discount              AS raw_net,
       total - COALESCE(discount, 0) AS net
FROM orders
WHERE order_id IN (1001, 1002);

Output:

 order_id | raw_net |  net   
----------+---------+--------
     1001 |  119.99 | 119.99
     1002 |         |  64.50
(2 rows)

Order 1002 returns 64.50 as net but an empty raw_net. Math with NULL gives NULL; COALESCE swaps in 0 first.

Example 2: Get the First Non-NULL Value Across Columns

Now the three phone columns in customers.

The goal is mobile, then work, then home, then a label. Column two also skips blanks:

SELECT name,
       COALESCE(mobile_phone, work_phone, home_phone, 'no phone') AS phone,
       COALESCE(NULLIF(mobile_phone, ''), work_phone, home_phone, 'no phone') AS fixed
FROM customers
ORDER BY customer_id;

Output:

      name      |    phone     |    fixed     
----------------+--------------+--------------
 Hannah Brooks  | 614-555-0142 | 614-555-0142
 Tyler Reed     | 512-555-0177 | 512-555-0177
 Grace Miller   | 305-555-0119 | 305-555-0119
 Marcus Bennett | no phone     | no phone
 Olivia Carter  |              | 206-555-0163
(5 rows)
PostgreSQL COALESCE returning the first non-NULL phone, and NULLIF skipping an empty mobile string
NULLIF skips Olivia’s blank.

Tyler returns his work number, and Marcus gets no phone. Olivia’s phone is blank, but fixed returns 206-555-0163.

Her mobile holds '', a real value in PostgreSQL (Oracle treats it as NULL). NULLIF turns it into NULL.

Drop NULLIF, run the SELECT again and you should see the blank return. It fits a PostgreSQL WHERE clause too.

Example 3: Use COALESCE with SUM and LEFT JOIN

Marcus Bennett has no orders yet.

Say you need sales per customer, with 0 for non-buyers. Here is the query:

SELECT c.name,
       SUM(o.total)              AS raw_sales,
       COALESCE(SUM(o.total), 0) AS sales
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE c.customer_id IN (1, 4)
GROUP BY c.customer_id, c.name
ORDER BY c.customer_id;

Output:

      name      | raw_sales | sales  
----------------+-----------+--------
 Hannah Brooks  |    194.49 | 194.49
 Marcus Bennett |           |      0
(2 rows)
PostgreSQL COALESCE(SUM(total), 0) after a LEFT JOIN returning 0 for a customer with no orders
SUM over no rows is NULL.

Marcus’s sales returns 0; raw_sales is empty.

SUM over zero rows is NULL, so wrap the aggregate. It runs after the PostgreSQL GROUP BY. I have a separate guide on joining several tables in PostgreSQL.

Example 4: Fix the “COALESCE Types Cannot Be Matched” Error

This one fails on purpose.

The goal is none for a missing discount. Two wrong tries first:

\set ON_ERROR_STOP off
\set VERBOSITY terse
SELECT COALESCE(discount, 'none') FROM orders;
SELECT COALESCE(customer_id, name) FROM customers;
SELECT order_id, COALESCE(discount::text, 'none') AS discount
FROM orders WHERE order_id = 1002;

Output:

psql:coalesce-type-mismatch.sql:3: ERROR:  invalid input syntax for type numeric: "none" at character 27
psql:coalesce-type-mismatch.sql:4: ERROR:  COALESCE types integer and character varying cannot be matched at character 30
 order_id | discount 
----------+----------
     1002 | none
(1 row)
PostgreSQL COALESCE errors: invalid input syntax for type numeric, and types integer and character varying cannot be matched
Two errors, one fix.

The cast version returns none for order 1002.

Every argument must convert to one type. ‘none’ was read as numeric; two mismatched columns fail outright. Cast first, as in PostgreSQL CAST to integer.

Example 5: Does COALESCE Short-Circuit?

Last, a function that announces each call.

I want proof that COALESCE skips arguments it doesn’t need:

CREATE OR REPLACE FUNCTION call_center(id int) RETURNS text
LANGUAGE plpgsql AS $$ BEGIN
  RAISE NOTICE 'ran for customer %', id;
  RETURN '800-555-0100';
END $$;
SELECT customer_id,
       COALESCE(mobile_phone, work_phone, call_center(customer_id)) AS phone
FROM customers WHERE customer_id BETWEEN 2 AND 4
ORDER BY customer_id;
SELECT COALESCE(mobile_phone, (1/0)::text) FROM customers;

Output:

psql:coalesce-short-circuit.sql:9: NOTICE:  ran for customer 3
psql:coalesce-short-circuit.sql:9: NOTICE:  ran for customer 4
 customer_id |    phone     
-------------+--------------
           2 | 512-555-0177
           3 | 800-555-0100
           4 | 800-555-0100
(3 rows)

psql:coalesce-short-circuit.sql:10: ERROR:  division by zero
PostgreSQL COALESCE short-circuit: call_center notices only for customers 3 and 4, then division by zero
Two calls, not three.

Notices print only for customers 3 and 4, the rows with no mobile or work phone.

Important: the last query still fails with division by zero, because PostgreSQL folds constants like 1/0 while planning. More in PostgreSQL functions that return a table.

Tips & Common Mistakes

  • PostgreSQL has no ISNULL, IFNULL or NVL: nvl(NULL, 1) fails with ERROR: function nvl(unknown, integer) does not exist.
  • End with a literal, or an all-NULL row still returns NULL.
  • 'Hi ' || NULL is NULL. Wrap each part, or use PostgreSQL CONCAT, which skips NULLs.
  • Cast mixed column types yourself, as in Example 4.

Wrapping Up

You’ll use COALESCE(column, default) most. Copy Example 3 for join reports and Example 2 for blanks.

Browse the complete list of PostgreSQL functions or the PostgreSQL 18 conditional expressions docs. Next step: the PostgreSQL SUM function.

Two more PostgreSQL guides worth a look:

Frequently Asked Questions

COALESCE vs NULLIF in PostgreSQL: what’s the difference?

NULLIF returns NULL when two values match. Its type can surprise: NULLIF(1, 2.2) is numeric, because only numeric = numeric fits that pair.

Can PostgreSQL use an index with COALESCE?

Not a plain index. WHERE COALESCE(discount, 0) = 0 needs an expression index on COALESCE(discount, 0).

Does COALESCE work with dates in PostgreSQL?

Yes: COALESCE(end_date, ‘infinity’::date) works, because dates accept infinity.