# PostgreSQL COALESCE() Function With Examples

> In PostgreSQL, the COALESCE() function handles the null values more efficiently. It accepts unlimited arguments and returns the first non-null argument.

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.

---
[View this page online](https://www.commandprompt.com/education/postgresql-coalesce-function-with-examples/)