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/:

• 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:

• After you create the database, you can use the command \c followed by testdb 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:

In this example, the output would be:

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:

The output will consist of the first non-NULL value in the list:

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.

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:

Here’s an example:

We can then use the INSERT statement to add a few records to our table:

The following syntax is used to insert records:

We’ll insert some sample data using the statement shown below:

We can confirm that the records are now in the table using the SELECT * FROM demo statement below:

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:

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.