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.
- 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.
Let’s start exploring Coalesce function in Postgres.
Example 1: Coalesce () function to return the First Non-Null Value
|
1 2 3 |
Select Coalesce(2,4,6,8,10) |
The following script does not contain any NULL values. Therefore, it returns the first non-null value, i.e., 2
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.
|
1 2 3 |
Select Coalesce(2,4,6,8,10,NULL) |
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.
|
1 2 3 |
Select Coalesce(NULL,2,4,6,8,10) |
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.
|
1 2 3 |
Select Coalesce(NULL, NULL ,2, NULL, 4,6,8,10, NULL) |
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.
|
1 2 3 |
Select Colaesce(NULL, NULL ,NULL, NULL) |
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.
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.
|
1 2 3 |
Select name, coalesce (bookname,'NA') from authortest; |
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.
|
1 2 3 |
Select Coalesce (NULL,1,'x') |
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.
|
1 2 3 |
Select 'Hello, My name is John ' || null || 'Peter' as the Name |
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.
|
1 2 3 |
Select 'Hello, My Name is John ' || coalesce (NULL,' ') || 'Peter' as “Name” |
We can replace empty strings with some characters, such as commas below.
|
1 2 3 |
Select 'Hello, My Name is John ' || coalesce (NULL,',') || 'Peter' as “Name” |
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.
|
1 2 3 4 |
Create table Marks(StudentName varchar(100),MarksObtained varchar(100); Insert into Marks values ('Raj',100),('Mohan',NULL),'Jai',75) |
The query below used coalesce function for the column [MarksObtained] and replaced the NULL with the text ‘Not appeared in Exam’.
|
1 2 3 |
Select StudentName, Coalesce (makrsobtained,'Not appeared in Exam') from marks |
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.
|
1 2 3 4 |
Create Table Products (ProductID int, AvailableStock int, SalesQuantity int); Insert into Products values (1,1000,590),(2,100,NULL),(3,1500,1299); |
The select statement calculates the remaining stock using subtraction between [AvailableStock] and [SalesQuantity].
|
1 2 3 4 5 6 |
Select ProductID, (AvailableStock-SalesQuantity) as RemainingBalance From Products; |
For the Product ID, the query does not have any remaining balance and returns NULL.
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]
|
1 2 3 4 5 6 |
Select ProductID, Coalesce(AvailableStock-SalesQuantity, AvailableStock) As RemainingBalance From Products; |
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.
|
1 2 3 |
create extension tablefunc; |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
Create table Products1(ProductID int, Month int,Quantity int); Insert into Products1 values (101,3,55),(101,5,68),(102,8,77), (102,9,91),(102,12,79); Select ProductID, "January", "February","March","April", "May","June","July","August", "September","October","November","December" FROM CROSSTAB ('Select ProductID,month,quantity from Products1 order by 1', 'Select m from generate_series(1,12) m' ) as (ProductID int, "January" int, "February" int, "March" int, "April" int, "May" int, "June" int, "July" int, "August" int, "September" int, "October" int, "November" int, "December" int); |
We can avoid NULLs with the Coalesce function and replace NULL with Zero using the following SQL.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
Select ProductID, Coalesce("Janurary",0), Coalesce("Feburary",0), Coalesce("March",0),Coalesce("April",0), Coalesce("May",0),Coalesce("June",0),Coalesce("July",0), Coalesce("August",0),Coalesce("September",0), Coalesce("October",0),Coalesce("November",0),Coalesce("December",0) FROM CROSSTAB ('Select ProductID,month,quantity from Products1 order by 1', 'Select m from generate_series(1,12) m' ) as (ProductID int, "January" int, "February" int, "March" int, "April" int, "May" int, "June" int, "July" int, "August" int, "September" int, "October" int, "November" int, "December" int); |
Here is the updated output you get from the Coalesce function in the Pivot table.
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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023