How to Use the PostgreSQL Coalesce Function Part 1
Introduction
If you’ve spent any time working with data in PostgreSQL, you know that NULL values can be difficult to handle. The presence of a NULL value in most expressions renders the entire expression NULL, and NULLs can yield odd results in comparisons as well. Fortunately, there’s a way to dodge these pitfalls and test for NULL values in your data. The COALESCE()
function takes a list of arguments and returns the first one that is not NULL. If all the arguments are NULL, the COALESCE()
function will return NULL. In this article, we’ll take a closer look at the PostgreSQL COALESCE function and look at some examples to better understand how the function can be used.
Prerequisites
A couple of important prerequisites need to be in place before proceeding with this tutorial:
You should have a basic knowledge of database management and common PostgreSQL commands.
You’ll need to have PostgreSQL installed and running. You can verify the PostgreSQL client server version by using the command
psql -V
in the terminal. You should see output that looks like this:
psql (PostgreSQL) 12.1 (Ubuntu 12.1-1.pgdg18.04+1)
The PostgreSQL COALESCE function
The PostgreSQL COALESCE function allows you to handle the null values when querying data so that you can use a substitute value instead.
The syntax for using the function is shown below/:
1 | COALESCE (args1, args2); |
- The
args
represents a list of values that are supplied to the function.
Access the interactive terminal for PostgreSQL
Before we can try some examples using the PostgreSQL COALESCE
function, we’ll need to access psql
— the interactive command-line terminal for PostgreSQL.
To enter the psql
terminal, you can use the sudo su - postgres
command if you’re running a Linux distribution that uses systemd
software.
After you enter your user password, use the command psql
to access the interface.
NOTE: You can use the psql database_name
command to access psql
and connect to a specific database if you’ve already created one.
Create database in PostgreSQL
If you need to create a database in PostgreSQL, you can use the following command:
1 | CREATE DATABASE testdb; |
- After you create the database, you can use the command
\c
followed bytestdb
to connect to it and and enter your query.
Using the COALESCE function in PostgreSQL
Let’s look at how the COALESCE
function works in conjunction with the SELECT
statement in PostgreSQL:
1 | SELECT COALESCE(3, NULL, 1); |
In this example, the output would be:
1 2 3 4 | COALESCE ---------- 3 (1 ROW) |
The command returns the value of 3
because the function finds the first non-NULL argument at the beginning of the list.
Let’s check out another example. This time, we’ll have three values in the list, and there will be no NULL values. Here’s what the query looks like:
1 | SELECT COALESCE('ball', 'court', 'field'); |
The output will consist of the first non-NULL value in the list:
1 2 3 4 | coalesce ---------- ball (1 row) |
Had the first value in this list been NULL, the COALESCE
function would have skipped it and returned the first value after it that was not NULL.
1 2 3 4 5 | SELECT COALESCE('ball', 'court', NULL, 'field'); COALESCE ---------- ball (1 ROW) |
1 2 3 4 5 | SELECT COALESCE(NULL, NULL, 'court', 'field'); COALESCE ---------- court (1 ROW) |
In the example shown above, you can see that the COALESCE
function skipped the first two values in the list because they were NULL and returned the non-NULL value ‘court’.
Keep in mind that if you use the PostgreSQL COALESCE function with with an invalid parameter, it will return an error:
ERROR: COALESCE types integer and timestamp with time zone cannot be matched LINE 1: SELECT COALESCE(5, now(), NULL);
The PostgreSQL COALESCE function example
We can also avoid inserting a NULL value into a table by using the COALESCE
function in PostgreSQL:
To create a PostgreSQL table, we use the syntax shown below:
1 | CREATE TABLE TABLE_NAME(column_name datatype CONSTRAINT[optional]); |
Here’s an example:
1 2 3 4 5 | CREATE TABLE demo( id INT PRIMARY KEY, name VARCHAR(50), age INT ); |
We can then use the INSERT
statement to add a few records to our table:
The following syntax is used to insert records:
1 | INSERT INTO TABLE_NAME(col1,col2) VALUES(val1, val2); |
We’ll insert some sample data using the statement shown below:
1 2 3 4 5 | INSERT INTO demo(id, name, age) VALUES(15, 'Michael', 20), (16, 'Sara', 23), (17, 'Lizzette', 21), (18, 'Carla', NULL); |
We can confirm that the records are now in the table using the SELECT * FROM demo
statement below:
1 2 3 4 5 6 7 | id | name | age ----+----------+----- 15 | Michael | 20 16 | Sara | 23 17 | Lizzette | 21 18 | Carla | (4 rows) |
Let’s assume that we’d like to prevent any NULL values from being inserted into this table. We can do this using the COALESCE
function:
1 2 3 4 5 6 7 8 | SELECT name, COALESCE(age, 22) name | COALESCE ----------+---------- Michael | 20 Sara | 23 Lizzette | 21 Carla | 22 (4 ROWS) |
The COALESCE
function substitutes the specified value ’22’ for any NULL values it encounters.
Conclusion
Working with NULL values in your data can be tricky– in many situations, it would be easier to identify and possibly substitute other values in their place. In this article, we showed you how it’s easy to bypass NULL values in your data using the PostgreSQL COALESCE function. Stay tuned for the second article in this series for more information on the COALESCE function and its uses.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started