SQL COALESCE in Postgres: A Simple Guide

In a database, we often find empty places in our tables. These are not zeros or spaces; they are special markers called NULL. A NULL value means “no data.” This can be a problem.

Let’s see how the SQL COALESCE function can help us solve this common issue using Postgres examples.

Imagine you have a small online shop. You have a table in your database called products. This table stores information about your products, like their name, their normal price, and a special discount price.

First, let’s create our products table.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    product_name VARCHAR(100),
    regular_price NUMERIC(10, 2),
    discount_price NUMERIC(10, 2)
);

Now, let’s add some data to our table. Notice that some products have a discount_price and some do not (their discount_price is NULL).

INSERT INTO products (product_name, regular_price, discount_price) VALUES
('Cool T-Shirt', 20.00, 15.00),
('Jeans', 50.00, NULL),
('Hat', 15.00, 12.50),
('Socks', 8.00, NULL);

Now our table is ready. We have some products with discounts and some without.

Let’s say you want to show your customers the final price. If a product has a discount, you show the discount_price. If it does not, you show the regular_price.

You might try to select the discount_price like this:

SELECT product_name, discount_price FROM products;

The result will look like this:

This is a problem. You cannot show “NULL” to your customers on your website. They will be confused. You need to show the regular_price when the discount_price is NULL.

One way to solve this is using a CASE statement. The CASE statement goes through conditions and returns a value when the first condition is met.

Here is how you can use CASE to show the correct price:

SELECT
    product_name,
    CASE
        WHEN discount_price IS NOT NULL THEN discount_price
        ELSE regular_price
    END AS final_price
FROM
    products;

This query says: check the discount_price. If it is not NULL, then use it. If it is NULL, then use the regular_price.

This works! The result is:

But the CASE statement can be long to write, especially if you have many conditions. This brings us to a better way. When we compare PostgreSQL Coalesce vs Case, we see that COALESCE is much simpler for this task.

The COALESCE function in PostgreSQL is a perfect and simple solution for this problem. It returns the first non-NULL value from a list of arguments.

The syntax is very easy: COALESCE(argument_1, argument_2, ...)

Let’s write our query again, but this time using COALESCE.

SELECT
    product_name,
    COALESCE(discount_price, regular_price) AS final_price
FROM
    products;

This query does the same thing as the CASE statement. It looks at discount_price first. If discount_price is not NULL, it returns that value. If it is NULL, it moves to the next argument, which is regular_price, and returns that value.

The result is exactly the same, but the query is much shorter and easier to read.

The power of COALESCE does not stop there. You can use it with many values. This is helpful when you need to coalesce multiple columns.

Let’s add a new column to our table called clearance_price.

ALTER TABLE products ADD COLUMN clearance_price NUMERIC(10, 2);

UPDATE products SET clearance_price = 10.00 WHERE product_name = 'Cool T-Shirt';

Now, the T-Shirt has a regular_price, a discount_price, and a clearance_price. We want to show the clearance_price if it exists. If not, the discount_price if it exists. If not, then the regular_price.

COALESCE makes this very easy.

SELECT
    product_name,
    COALESCE(clearance_price, discount_price, regular_price) AS final_price
FROM
    products;

The function will check each column in order (clearance_price, then discount_price, then regular_price) and return the very first price that is not NULL.

The SQL COALESCE function is a powerful tool in PostgreSQL. It helps you write clean, simple, and readable SQL. When you need to replace NULL with a default value, COALESCE is often the best choice.

It is simpler than writing long CASE statements and makes your code better. So next time you have a problem with NULL values, remember the SQL COALESCE function.

Finally, if you found this article useful, you can check more here:

• How to Inner Join 3 Tables in SQL
• Facade vs Proxy vs Adapter Design Patterns
• TypeScript Type Vs Interface? The Answer Is Type!