PostgreSQL: COALESCE function:

The syntax of the COALESCE function is as follows:

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.

The 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.

We often use the COLAESCE function to substitute a default value for null values when we querying the data.

CREATE TABLE foo (
   ID INT PRIMARY KEY,
   NAME VARCHAR (50)
);

INSERT INTO foo (ID, NAME)
VALUES
   (1, 'a'),
   (2, 'b'),
   (3, null),
   (4, null);

select id, COALESCE (name, 'Test') from foo

PostgreSQL COALESCE example:

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

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

Now we need to find out the net prices of the product after discount:

net_price = price - discount;

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

If 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;

Now the net price of product D is 500 because we use zero instead of null value when we calculated the net price.

Note: If we want to convert 0 to null, then use NULLIF, while if we want to convert null to 0, then use COALESCE.