PostgreSQL COALESCE

Summary: in this tutorial, you will learn about the PostgreSQL COALESCE() function that returns the first non-null argument.

PostgreSQL COALESCE function syntax

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

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

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

The COALESCE() function accepts multiple arguments and 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 the IFNULL function whereas Oracle Database offers the NVL function.

PostgreSQL COALESCE() Function examples

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

1) Basic PostgreSQL COALESCE() function examples

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

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

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

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

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

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

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 the COLAESCE() 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 the COALESCE function as follows:

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

2) Using the COALESCE() function with table data

First, create a table called items:

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)

The items table has four columns:

  • 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, insert some rows into the items table:

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, retrieve the net prices of the products from the items table:

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

Output:

 product | net_price
---------+-----------
 A       |       990
 B       |      1480
 C       |       795
 D       |      null
(4 rows)Code language: JavaScript (javascript)

The output indicates that the net price of the product D is null.

The issue is that the discount of the product D 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 the COALESCE() function in the query as follows:

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

Output:

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

Now the net price of the product D is 500 because the query uses zero instead of NULL when calculating the net price.

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

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

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 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, the COALESCE() function and CASE expression are the same.

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

Summary

  • Use the COALESCE() function to substitute null values in the query.
Was this tutorial helpful ?