Rajendra Gupta
New database

Learn the PostgreSQL COALESCE command

January 19, 2023 by

This article will make you learn the Postgres Coalesce command with examples.

Introduction

The coalesce function in PostgreSQL is beneficial when we want to ignore null values while processing data. As we all know, we can insert both null and non-null values into a PostgreSQL table. However, in many cases, we don’t want those null values. That’s where coalesce comes in. Its purpose is to display the first non-null value it encounters. In this discussion, we’ll primarily explore coalesce usage in PostgreSQL.

Coalesce in PostgreSQL

The coalesce function’s role is to return the first non-null value it encounters when reading from left to right. In addition, it can replace null values with a specified non-null value. The Coalesce function requires a minimum of two inputs.

The syntax of the PostgreSQL COALESCE function is as given below:

COALESCE (x,y……,n);

The Coalesce function is a shorthand of the Case statement.

CASE
WHEN x IS NOT NULL THEN x
WHEN y IS NOT NULL THEN y
WHEN … IS NOT NULL THEN …
ELSE n
END

Before we start exploring the Coalesce function using examples, you should have the following setup.

  • Postgres installation: This article uses PostgreSQL on Ubuntu. You can visit https://www.postgresql.org for installation. For this article, I use PostgreSQL 14.5, as shown below.

Postgres installation

  • PgAdmin4:The PgAdmin4 is a graphical and open source tool for working with the Postgres database. You can use it for database development, maintenance, and use of database objects. Go to the URL https://www.pgadmin.org to install it.
  • Create a new database for executing the queries. I used the Postgres database [DemoDb] for this article, as shown below.

New database

Let’s start exploring Coalesce function in Postgres.

Example 1: Coalesce () function to return the First Non-Null Value

The following script does not contain any NULL values. Therefore, it returns the first non-null value, i.e., 2

Learn Postgres COALESCE command

Example 2: NULL value as the last argument in the coalesce function

Let’s add the NULL as the last argument in the previous example and check the query output. The function output is still value 2 because it returns the first non-null, i.e., value 2 in our example.

NULL value as the last argument in the coalesce function

Example 3: NULL value as the first argument in the coalesce function

Let’s say the NULL is the first argument in the following example. The function output is still 2 because of the first non-null value.

NULL value as the first argument in the coalesce function

Example 4: Using Coalesce with multiple Null Values

In the following query, we have NULL in the first, second, fourth, and last positions. The Coalesce function ignores the first two NULLs and returns the first non-null value, i.e., 2. It does not check the following NULL values.

Using Coalesce with multiple Null Values

Example 5: Coalesce function having all NULL arguments

Suppose the supplied values do not have non-null values. In this case, the coalesce function returns null as well. It is like an exception where the coalesce function returns NULL.

Coalesce function having all NULL arguments

Example 6: Using Coalesce function with the tables in Postgres

In most cases, we use the functions with the values stored in the tables. Let’s say we have the following table that contains both NULL and non-null values.

Using Coalesce function with the tables in Postgres

The following query uses coalesce() function for the bookname column. In case of null, it displays NA (non-null) value. The rest of the values (non-null) shows the actual value. We do not want to display the null value if anyone selects the values from the author’s table.

NA for non-null values

Example 7: Using Coalesce function with different data type arguments in Postgres

If you specify arguments with different datatypes, they should be convertible. Else the coalesce function returns an error, as shown below.

Coalesce function with different data type arguments in Postgres

Example 8: Concatenating string using Coalesce function

String concatenation is a common task in applications. Suppose you want to display the customer’s full name if you have the customer’s first, middle, and last name. Many customers might not have a middle name. In this case, the string concatenation returns NULL if any of the strings contain NULL, as shown below.

Concatenating string using Coalesce function

To avoid the situation, we can use Coalesce function to return space or an empty string if NULL is found. The following query replaces the NULL value with an empty string.

Hello my name is John, Coalesce example

We can replace empty strings with some characters, such as commas below.

Coalesce example

Example 9: Display a string or value in case of a NULL

Suppose you have a table that contains marks obtained by the students in a recent exam. If a student did not appear for the examination, its marks column contains NULL.

Now, if we query the table, it should show student did not appear in the exam instead of NULL. We can do it using the coalesce function.

Let’s create a marks table and insert a few records.

The query below used coalesce function for the column [MarksObtained] and replaced the NULL with the text ‘Not appeared in Exam’.

Display a string or value in case of a NULL

Example 10: Use Coalesce function for calculated values

The calculated columns do calculations during query executions and return data as a separate column. Let’s see the impact of NULL in the case of calculated columns. I created a table called [Products] for this example and inserted a few records. The products table contains the available stock and sales quantity. You may notice that product id 2 has NULL in the sales quantity column.

Learn Postgres Coalesce function for calculated values

The select statement calculates the remaining stock using subtraction between [AvailableStock] and [SalesQuantity].

For the Product ID, the query does not have any remaining balance and returns NULL.

Learn Postgres Coalesce function for calculated columns

We require that if the [SalesQuantity] column has NULL values, the calculated column should show the [AvailableStock] value. We can use the Coalesce() function for the requirement.

The Coalesce function returns the value as below.

  • In case of a non-null value from the argument AvailableStock-SalesQuantity, it returns the value returned.
  • In case of NULL value from argument AvailableStock-SalesQuantity, it returns the value of [AvailableStock]

NULL value from argument

Example 11: COALESCE with the PIVOT tables

Pivot tables are a popular tool to analyze data and visualize comparisons, patterns, and trends in the data. You might have used the pivot tables in Microsoft Excel before. In PostgreSQL, we can use the CROSSTAB function that is part of the PostgreSQL extension tablefunc. Run the following statement to enable the CROSSTAB function.

Let’s create a new table and insert the records with data for specific months. As we do not have data for a few months, we get NULL values for the months that do not have any data in the Pivot table.

COALESCE with the PIVOT tables

We can avoid NULLs with the Coalesce function and replace NULL with Zero using the following SQL.

Here is the updated output you get from the Coalesce function in the Pivot table.

Learn Postgres COALESCE with the PIVOT tables example

Conclusion

In this blog, we discussed how to use the Postgres COALESCE command. The COALESCE command returns the first non-null value from the supplied input arguments. You should use the function where you predict the NULL values can be a problem.

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
343 Views