PostgreSQL provides a function named COALESCE() that handles the null values more efficiently. The COALESCE () function is used in PostgreSQL to get the first non-null argument/value. In a certain case, where all arguments are null, the COALESCE() function will return a null value.
This post will present detailed knowledge about the Postgres COALESCE() function with the help of different examples. So, let’s begin.
How to Use COALESCE() Function With Examples?
The below snippet illustrates the syntax of the COALESCE() function:
COALESCE (arg_1, arg_2, ...);
The syntax shows that the COALESCE() function can accept unlimited arguments. It starts the argument evaluation from left to right. Once the COALESCE() function locates the first non null value/argument, then, it will stop further evaluation. As a result, it will return only the first non-null value/argument. Let’s understand the working of the Postgres COALESCE() function with examples.
Example #1: Pass All the Non-Null Arguments to COALESCE()
In this example, we will assign five non-null arguments to the COALESCE() function as follows:
SELECT COALESCE(110, 12, 525, 910, 006);
The COALESCE() function finds the non-null argument on the very first index, so it stopped further evaluation and returned the first non-null value, i.e., “110”.
Example #2: Pass Null Argument to COALESCE()
In this example, we will explore how the COALESCE function deals with the NULL arguments:
SELECT COALESCE(NULL, 525, 910, 006);
The output shows that the COALESCE() function returns the first non-null value, i.e., ‘525’.
Example #3: Pass Multiple Null Argument to COALESCE()
Let’s explore how the COALESCE() function deals with the multiple NULL arguments:
SELECT COALESCE(NULL, NULL, 910, 525, 006);
The COALESCE() function skipped all the NULL values and returned the first non-null value, i.e., ‘925’.
Example #4: How to Use COALESCE() Function With Table’s Data?
We created a table named emp_data in our existing database and retrieved the table’s details using the SELECT statement:
SELECT * FROM emp_details;
Let’s calculate the total salary by adding the bonus to the basic salary:
SELECT emp_salary + emp_bonus AS "total_salary" FROM emp_details;
From the output, it is clear that we got the faulty results. The sum of emp_salary + NULL should be emp_salary; however, we got NULL instead of emp_ salary. To get accurate results, we will use the COALESCE() function as follows:
SELECT emp_salary + COALESCE(emp_bonus, 0) AS total_salary FROM emp_details;
The above snippet served the following functionalities:
- Performed addition over the emp_salary and emp_bonus columns.
- Utilized the COALESCE() function to deal with the NULL values.
- In the COALESCE() function, we passed 0 as the second argument.
- So, whenever a NULL value occurs in the emp_bonus column, the COALESCE() function will return 0 instead of a NULL value:
The COALESCE() function provides accurate results. This way, the COALESCE() function assists us in dealing with the NULL values.
Conclusion
In PostgreSQL, the COALESCE() function handles the null values more efficiently. It accepts unlimited arguments and retrieved the first non-null argument/value. In the case of all NULL arguments, the COALESCE() function returns a null value. It starts the argument evaluation from left to right. Once the COALESCE() function locates the first non-null argument, it will stop the further evaluation, and it will return only the first non-null argument. This write-up considered some examples to explain different use cases of the COALESCE() function in PostgreSQL.
