PostgreSQL COALESCE function syntax

TheCOALESCE() function accepts a list of arguments and returns the first non-null argument.

Here’s the basic syntax of theCOALESCE() function:

COALESCE (argument_1,argument_2, …);

TheCOALESCE() function accepts multiple arguments and returns the first argument that is not null. If all arguments are null, theCOALESCE() function will return null.

TheCOALESCE() 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.

TheCOALESCE function provides the same functionality asNVL orIFNULL function provided by SQL standard. MySQL has theIFNULL function whereas Oracle Database offers theNVL function.

PostgreSQL COALESCE() Function examples

Let’s take some examples of using theCOALESCE() function.

1) Basic PostgreSQL COALESCE() function examples

The following example uses theCOALESCE() function to return the first non-null argument:

SELECT COALESCE (1,2);

Since both arguments are non-null, the function returns the first argument:

coalesce----------        1(1 row)

The following example uses theCOALESCE() function to return the first non-null argument:

SELECT COALESCE (NULL,2 , 1);

Because the first argument is NULL and the second argument is non-null, the function returns the second argument:

coalesce----------        2(1 row)

In practice, you often use theCOLAESCE() function to substitute a default value for null when querying data from nullable columns.

For example, if you want to display the excerpt from a blog post and the excerpt is not provided, you can use the first 150 characters of the content of the post.

To achieve this, you can use theCOALESCE function as follows:

SELECT  COALESCE (    excerpt,    LEFT(content, 150)  )FROM  posts;

2) Using the COALESCE() function with table data

First,create a table calleditems:

CREATE TABLE items (  idSERIAL PRIMARY KEY,  productVARCHAR (100)NOT NULL,  priceNUMERIC NOT NULL,  discountNUMERIC);

Theitems table has four columns:

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

Second,insert some rows into theitems table:

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

Third, retrieve the net prices of the products from theitems table:

SELECT  product,  (price- discount)AS net_priceFROM  items;

Output:

product | net_price---------+----------- A       |       990 B       |      1480 C       |       795 D       |      null(4 rows)

The output indicates that the net price of the productD is null.

The issue is that thediscount of the productD is null. Therefore, the net price is NULL because it involves NULL in the calculation.

With an assumption that if the discount is null, the net price is zero, you can use theCOALESCE() function in the query as follows:

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

Output:

product | net_price---------+----------- A       |       990 B       |      1480 C       |       795 D       |       500(4 rows)

Now the net price of the productD is500 because the query uses zero instead of NULL when calculating the net price.

Besides using theCOALESCE() function, you can use theCASE expression to handle the NULL in this example.

For example, the following query uses theCASE expression to achieve the same result:

SELECT  product,  (    price - CASE WHEN discount IS NULL THEN 0 ELSE discount END  )AS net_priceFROM  items;

In this query, if the discount is null then use zero (0) otherwise use the discount value to calculate the net price.

In terms of performance, theCOALESCE() function andCASE expression are the same.

It is recommended to useCOALESCE() function because it makes the query shorter and easier to read.

Summary

• Use theCOALESCE() function to substitute null values in the query.

Last updated onFebruary 1, 2024

On this page

• PostgreSQL COALESCE function syntax
• PostgreSQL COALESCE() Function examples
• Summary