Postgres offers an inbuilt COALESCE() function that deals with the null values. It takes n arguments and retrieves the first non-null value. The COALESCE() function accepts the “n” number of arguments and retrieves the first non-null value. If all the passed values are NULL, then the COALESCE() function will retrieve a null value.

This blog post will provide a detailed overview of how to get the first non-null value in PostgreSQL. So, let’s get started.

How to Get the First Non-Null Value in Postgres?

To get the first non-null value in Postgres, an inbuilt function named COALESCE() is used:

COALESCE (val_1, val_2, ...);

• Here, val_1, val_2, etc. are the arguments that can be null or non-null.
• The COALESCE() function starts the argument’s evaluation from the left side(first value) and searches for the first non-null value.
• Once the COALESCE() function finds a non-null value, immediately, it will stop the evaluation. Consequently, It retrieves only the first non-null value.

Let’s comprehend the functionality of the Postgres COALESCE() function via practical examples.

Example #1: Passing String Values

In this example, we will assign five non-null arguments to the COALESCE() function as follows:

SELECT COALESCE('Mike', 'Joe', 'Seth', 'Ambrose', 'Joseph');

The COALESCE() function found a non-null string at the very first index, so it stopped evaluation immediately and retrieved that non-null string, i.e., “Mike”.

Example #2: Passing Null and Non-Null Strings

In the below example, we will pass null as well as non-null strings to the COALESCE function:

SELECT COALESCE(NULL, NULL, 'Joe', 'Seth', 'Mike', NULL, 'Ambrose', 'Joseph');

The output snippet proves that the COALESCE() function skipped the first two NULL strings, and retrieved the first non-null string, i.e., ‘Joe’.

Example #3: How Does COALESCE() Function Work in Postgres?

Let’s create a sample table named product_details with four columns: pro_id, pro_name, pro_price, pro_tax:

CREATE TABLE product_details(
pro_id SERIAL,
pro_name TEXT,
pro_price INT,
pro_tax INT
);

The table named product_details has been created successfully. Let’s insert the below-listed records into the newly created table:

INSERT INTO product_details(pro_name, pro_price, pro_tax)
VALUES ('Laptop', 50000, 5000),
('Mobile', 50000, NULL),
('Bike', 50000, 1000);

Three records have been inserted into the product_details table. To verify the newly inserted data, execute the SELECT statement as follows:

SELECT * FROM product_details;

Let’s find out the total price of a product:

SELECT pro_price + pro_tax AS grand_total
FROM product_details;

We performed additions on the “pro_price” and “pro_tax” columns. As a result, we got erroneous results in the case of a NULL value. To resolve such an issue, we can utilize the COALESCE() function as follows:

SELECT pro_name, pro_price, pro_tax, pro_price + COALESCE(pro_tax, 0) 
FROM product_details;

This is how the COALESCE() function deals with the null values.

Conclusion

To get the first non-null value in Postgres, an inbuilt function named COALESCE() is used. COALESCE() is an inbuilt function that deals with the null values. The COALESCE() function accepts the “n” number of arguments and retrieves the first non-null value. If all the passed values are NULL, then the COALESCE() function will retrieve a null value. This blog post explained how to get the first non-null value in Postgres.