How to Use the PostgreSQL COALESCE Function Part 2

Introduction

When you’re managing data in PostgreSQL, you’ll find that NULL values can be a bit tricky to handle. For example, most expressions return a value of NULL when any element of the expression is NULL. How can we avoid some of the difficulties associated with NULL values? In the first article of this two-part series, we explained how the PostgreSQL COALESCE function can be used to test for NULL values and substitute another value in place of the NULLs. This article will pick up where we left off and continue our discussion of the COALESCE function and its applications in PostgreSQL.

Prerequisites

Before attempting to follow along with the examples in this article, be sure that PostgreSQL is already installed on your machine. You should also have some introductory knowledge of PostgreSQL in order to understand the examples of the COALESCE function.

COALESCE in psql command-line interface

In the previous article, we explained how to access the psql command-line interface for PostgreSQL. We’ll need to enter the psql command line console once again to try out some COALESCE examples.

Let’s start with the simple example shown below:

1
SELECT COALESCE ( 'apple' , 'pear' , 'strawberry');

The output of this query will be:

1
2
3
COALESCE
----------
apple

As you can see, the returned output is ‘apple’ as it’s the first non-NULL value in the argument list.

If we have a NULL value as the first item of the list, the COALESCE function will skip it, looking for the first value that is not NULL. We can see how this works in the following query:

1
SELECT COALESCE ( NULL, 'apple' , 'pear' , 'strawberry');

The output will be:

1
2
3
4
COALESCE
----------
apple
(1 ROW)

Use PostgreSQL COALESCE timestamp null

In our next example, we’ll try to use the COALESCE function with the timestamp data type.

First, we’ll need to create another table in PostgreSQL:

1
2
3
4
5
6
7
CREATE TABLE books(
book_id INT PRIMARY KEY NOT NULL,
book_name VARCHAR NOT NULL,
quantity INT,
price REAL,
year_released TIMESTAMP
);

We’ll also need to insert records into the books table we just created:

1
2
3
4
5
6
7
INSERT INTO books(book_name, quantity, price, year_released)
VALUES
('Book1', 12, 60, '2015-07-21 09:10:25+8'),
('Book2', 5, 55, '2018-02-12 15:40:15+8'),
('Book3', 10, 90, '2017-11-12 00:10:11+8'),
('Book4', 26, 47, NULL),
('Book5', 2, 83, '2019-03-05 03:05:08+8');

Notice that we inserted a NULL value into our table in the year_released column, which has a data type of timestamp.

Let’s imagine that we’d like to get the total price of each book. To get that information, we’ll multiply the values of two columns: quantity and price. The query below shows how this is done:

1
2
SELECT book_id, book_name, ( quantity * price ) AS total_price
FROM books;

The output of the query will look like:

1
2
3
4
5
6
7
8
book_id | book_name | total_price
---------+-----------+-------------
1 | Book1 | 720
2 | Book2 | 275
3 | Book3 | 900
4 | Book4 | 1222
5 | Book5 | 166
(5 ROWS)

Let’s look at the contents of the books table with this query:

1
SELECT * FROM books;

The output would look like this:

1
2
3
4
5
6
7
8
book_id | book_name | quantity | price | year_released
---------+-----------+----------+-------+---------------------
1 | Book1 | 12 | 60 | 2015-07-21 09:10:25
2 | Book2 | 5 | 55 | 2018-02-12 15:40:15
3 | Book3 | 10 | 90 | 2017-11-12 00:10:11
4 | Book4 | 26 | 47 |
5 | Book5 | 2 | 83 | 2019-03-05 03:05:08
(5 rows)
  • Notice that there is a NULL value in the last column of the fourth row. We can change this to use a non-NULL value instead using the COALESCE function:
1
2
SELECT book_id, book_name,
COALESCE(year_released, CURRENT_TIMESTAMP) FROM books;

Here’s what the output will look like:

1
2
3
4
5
6
7
8
book_id | book_name | COALESCE
---------+-----------+-------------------------------
1 | Book1 | 2015-07-21 09:10:25-04
2 | Book2 | 2018-02-12 15:40:15-05
3 | Book3 | 2017-11-12 00:10:11-05
4 | Book4 | 2020-02-13 06:20:41.239926-05
5 | Book5 | 2019-03-05 03:05:08-05
(5 ROWS)

Conclusion

In this two-part article series, we’ve taken an in-depth look at the PostgreSQL COALESCE function. We’ve shown several examples of how this function can be used to test for NULL values in your data and substitute a non-NULL value if needed. Using these examples as a guide, you’ll be prepared to incorporate the COALESCE function into your own PostgreSQL statements.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.