Open In App

PostgreSQL – COALESCE

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

In PostgreSQL, the COALESCE function returns the first non-null argument. It is generally used with the SELECT statement to handle null values effectively.

Syntax: COALESCE (argument_1, argument_2, …);

The COALESCE function accepts an unlimited number of arguments. It returns the first argument that is not null. If all arguments are null, the COALESCE function will return null. he COALESCE function evaluates arguments from left to right until it finds the first non-null argument. All the remaining arguments from the first non-null argument are not evaluated.

Example:
For the purpose of demonstration let’s first create a table (say, items) using the below statement:

CREATE TABLE items (
    ID serial PRIMARY KEY,
    product VARCHAR (100) NOT NULL,
    price NUMERIC NOT NULL,
    discount NUMERIC
);

There are four fields in the items table:

  • id: the primary key that identifies the item in the items table.
  • product: the product name.
  • price: the price of the product.
  • discount: the discount of the product.

Now we insert some records into the items table using INSERT statement as follows:

INSERT INTO items (product, price, discount)
VALUES
    ('A', 1000, 10),
    ('B', 1500, 20),
    ('C', 800, 5),
    ('D', 500, NULL);

Finally we query the net prices of the products using the following:

SELECT
    product,
    (price - discount) AS net_price
FROM
    items;

This will lead to the following:

f you look at the fourth row, you will notice that the net price of the product D is null which seems not correct. The issue is the discount of the product D is null, therefore when we take the null value to calculate the net price, PostgreSQL returns null. The get the right price, we need to assume that if the discount is null, it is zero. Then we can use the COALESCE function as follows:

SELECT
    product,
    (price - COALESCE(discount, 0)) AS net_price
FROM
    items;

Output:


Last Updated : 28 Aug, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads