PostgreSQL COALESCE

Summary: in this tutorial, you will learn about the PostgreSQL COALESCE function that returns the first non-null argument. You will learn how to apply this function in a SELECT statement to handle null values effectively.

PostgreSQL COALESCE function syntax

The syntax of the COALESCE function is as follows:

COALESCE (argument_1, argument_2, …);Code language: SQL (Structured Query Language) (sql)

The COALESCE function accepts an unlimited number of arguments. It returns the first argument that is not null. If all arguments are null, the COALESCE function will return null.

The COALESCE function evaluates arguments from left to right until it finds the first non-null argument. All the remaining arguments from the first non-null argument are not evaluated.

The COALESCE function provides the same functionality as NVL or IFNULL function provided by SQL standard. MySQL has IFNULL function, while Oracle provides NVL function.

See the following examples:

SELECT
	COALESCE (1, 2);Code language: SQL (Structured Query Language) (sql)

SELECT
	COALESCE (NULL, 2 , 1);Code language: SQL (Structured Query Language) (sql)

We often use the COLAESCE function to substitute a default value for null values when we queried the data. For example, if we want to display the excerpt from a blog post, if the excerpt is not provided, we can use the first 150 characters the of the content of the post. To achieve this, we can use the COALESCE function as follows:

SELECT
	COALESCE (excerpt, LEFT(CONTENT, 150))
FROM
	posts;Code language: SQL (Structured Query Language) (sql)

PostgreSQL COALESCE example

Let’s take a look at an example of using COALESCE function. First, we create a table named items using the CREATE TABLE statement as follows:

CREATE TABLE items (
	ID serial PRIMARY KEY,
	product VARCHAR (100) NOT NULL,
	price NUMERIC NOT NULL,
	discount NUMERIC
);Code language: SQL (Structured Query Language) (sql)

There are four fields in the items table:

• id: the primary key that identifies the item in the items table.
• product: the product name.
• price: the price of the product.
• discount: the discount on the product.

Second, we insert some records into the items table using the INSERT statement as follows:

INSERT INTO items (product, price, discount)
VALUES
	('A', 1000 ,10),
	('B', 1500 ,20),
	('C', 800 ,5),
	('D', 500, NULL);Code language: SQL (Structured Query Language) (sql)

Third, we query the net prices of the products using the following formula:

net_price = price - discount;Code language: SQL (Structured Query Language) (sql)

SELECT
	product,
	(price - discount) AS net_price
FROM
	items;Code language: SQL (Structured Query Language) (sql)

If you look at the fourth row, you will notice that the net price of the product D is null which seems not correct. The issue is the discount of the product D is null, therefore when we take the null value to calculate the net price, PostgreSQL returns null.

The get the right price, we need to assume that if the discount is null, it is zero. Then we can use the COALESCE function as follows:

SELECT
	product,
	(price - COALESCE(discount,0)) AS net_price
FROM
	items;Code language: SQL (Structured Query Language) (sql)

Now the net price of product D is 500 because we used zero instead of null value when we calculated the net price.

Besides using the COALESCE function, you can use the CASE expression to handle the null values in this case. See the following query that uses the CASE expression to achieve the same result above.

SELECT
	product,
	(
		price - CASE
		WHEN discount IS NULL THEN
			0
		ELSE
			discount
		END
	) AS net_price
FROM
	items;
Code language: SQL (Structured Query Language) (sql)

In the query above we say if the discount is null then use zero (0) otherwise use the discount value in the expression that calculates the net price.

In terms of performance, COALESCE function and CASE expression are the same. We prefer COALESCE function to CASE expression because COALESCE function makes the query shorter and easier to read.

Summary

• Use the COALESCE function to substitute null values in the query.