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 [, ...])
| Argument | Required | What it means |
|---|---|---|
value | Yes, one or more | Any 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)

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)

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)

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

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 withERROR: function nvl(unknown, integer) does not exist. - End with a literal, or an all-NULL row still returns NULL.
'Hi ' || NULLis 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.

Kumar Saurabh is a freelance software engineer who builds web and mobile apps with Next.js, React, Flutter and Python. From 2021 to 2024, Saurabh worked as an IT analyst and developer at TSInfo Technologies, writing technical articles on SQL and PostgreSQL. Saurabh writes the PostgreSQL, MySQL, MariaDB and MongoDB tutorials on DatabaseFAQs.com and holds a Master of Computer Applications (MCA) from Chandigarh University.