PostgreSQL

What is Coalesce in PostgreSQL?

Coalesce is a very useful function in PostgreSQL in Windows 10. We all know that we can insert both types of values in the PostgreSQL table, i.e., null or non-null. However, at times, we do not want to see those null values while processing our data. In this case, the coalesce function can be used whose purpose is to display the first non-null value that it encounters. This discussion will mainly revolve around exploring the usage of the coalesce function in PostgreSQL in Windows 10.

What is Coalesce in PostgreSQL in Windows 10?

The basic usage of the coalesce function is simply to return the first non-null value that it encounters while reading from left to right. However, apart from this basic usage, this function can also replace the null values that it encounters with any desired non-null value specified by the programmer. We will explore this usage too in one of the instances shared in this article.

How to Use Coalesce in PostgreSQL in Windows 10?

The following four examples are there to demonstrate the usage of Coalesce in PostgreSQL in Windows 10:

Example 1: Making Coalesce to Return the First Non-Null Value
We can use coalesce to return the first non-null value out of all the provided values. The query shown below will elaborate this:

# SELECT coalesce(1,2,3,4,5);

We have passed five numbers to the coalesce function in this query. In other words, all the values passed to the coalesce function in this example are non-null.

Since the coalesce function in PostgreSQL always returns the first non-null value; therefore, the result of this query will be “1,” as shown in the following appended image:

Example 2: Using Coalesce with Some Null Values
Now, we will try to pass some null values too to the coalesce function with the help of the query shown below to see how it affects our results:

# SELECT coalesce(NULL,NULL,3,4,5);

You can see that in this query, the first two values that have been passed to the coalesce function are null, whereas the first non-null value is “3”.

Therefore, the result of this query will be “3” since it is the first non-null value that has been passed to the coalesce function. This is shown in the following appended image:

Example 3: Using Coalesce with all the Null Values
An important thing that we would like to share over here is that the coalesce function is designed to return the first non-null value that it encounters by default. However, this functionality will surely change if all the null values are passed to the coalesce function. This is shown in the query stated below:

# SELECT coalesce(NULL,NULL,NULL);

In this query, we have passed all the null values to coalesce function to figure out what the coalesce function will return in this case.

You can see from the following output that the coalesce function has not returned any output after the execution of this query, or in other words, the output is null. It means that the coalesce function returns a null value if all the values provided to this function are null. Otherwise, it will always return the first non-null value.

Example 4: Using Coalesce with a Table in PostgreSQL
We want to explore a relatively complex usage of the coalesce function, i.e.; we want to use this function with a PostgreSQL table. You can learn this by going through the steps shown below:

Step 1: Create a PostgreSQL Table
For this example, we will first create a sample PostgreSQL table with the help of the following query:

# CREATE TABLE demo(Name VARCHAR (255) NOT NULL, ID INT);

This query will create a table named “demo” with two different attributes or columns, i.e., a name whose value cannot be null and an ID. We have intentionally not used the NOT NULL flag with the ID attribute because we will pass on some null values to this column later in this example.

Once this query executes, a PostgreSQL table with the title “demo” will be created on your server.

Step 2: Insert Values into the PostgreSQL Table
Now, it is time for inserting values into this table. We can also insert the records one by one or even execute a single query to insert all the records at once. We are going to adopt the latter approach this time, i.e., we will be inserting all the records at once with a single PostgreSQL query shown below:

# INSERT INTO demo VALUES(‘Aqsa’,1), (‘Saeed’, NULL), (‘Ramsha’,3);

With the help of this query, we have attempted to insert 3 different records into the “demo” table. However, in the second record, you can see that we have kept the value of the ID column as null.

The following output will be displayed on the console upon successfully inserting the records into the “demo” table.

Step 3: Display all the Values of the PostgreSQL Table
Once we have inserted the desired records into the PostgreSQL table, we can display them all with the query shown below:

# SELECT * from the demo;

This query will simply display all the records of the “demo” table as follows:

Alternatively, you can also execute the query shown below to get the same results:

# SELECT Name, ID from the demo;

The output for this query is just the same as we have shared above.

You can notice from this output that we have a null value in our records. However,, we might not want to see this null value; rather, we want it to be replaced by an integer. So, to achieve this objective, you will have to perform the next step.

Step 4: Use the Coalesce Function with the PostgreSQL Table
Now, we will use the coalesce function in PostgreSQL to fix the issue mentioned above. This fix is hidden in the following query:

# SELECT Name, coalesce(ID,0) from demo;

This query will simply replace the null value or values of the ID column with a “0”.

In this way, when this query is executed, you will see a “0” instead of the null value, whereas the rest of the values will stay intact, as shown in the image below:

Conclusion

This article was all about the usage of the coalesce function in PostgreSQL in Windows 10. To demonstrate this, we created different examples that revolved around using the coalesce function with different values. Moreover, we also tried to learn how this function can replace the null values with some specified non-null values. Once you go through this guide, you will understand the usage of this PostgreSQL function in Windows 10. Additionally, you will also be able to use the coalesce function effectively to return the first non-null value or replace the null values with a non-null value.

About the author

Aqsa Yasin

I am a self-motivated information technology professional with a passion for writing. I am a technical writer and love to write for all Linux flavors and Windows.