PostgreSQL COALESCE Function: Handling NULL Values Effectively
Tags: coalesce, postgres
Published: 2023-08-31 07:00:00

**Let’s explore the COALESCE PostgreSQL function, taking a look at what it is, what it does, its pros and cons, and when to use it to handle NULL values in some real-world examples.**

---

### Tools used in this tutorial

[DbVisualizer](https://www.dbvis.com/download/), top rated database management tool and SQL client
The [PostgreSQL](https://www.postgresql.org/) Database

---
Dealing with `NULL` values is a headache. Neglecting them can result in missing data in the frontend of your application. You could address them at the application level, but that would result in performance overhead and potential data inconsistencies. Why not replace them with placeholder values directly in the queries? Well, this is what `COALESCE` is all about!

In this article, you will find out everything you need to know about `COALESCE`, and see how it can help you build `NULL`-safe applications.

## What Is COALESCE in PostgreSQL?

`COALESCE` is a PostgreSQL function that accepts multiple arguments and returns the first non-`NULL` value. It returns NULL only if all elements in the argument list are `NULL`. The function is particularly useful when you want to replace missing data with meaningful alternatives or need to perform conditional operations based on the presence of `NULL` values. In other words, it is a versatile tool that allows you to properly handle `NULL` values in your PostgreSQL queries.

## How to Use COALESCE in PostgreSQL

The COALESCE syntax in PostgreSQL is pretty straightforward:

```
1 COALESCE(value [, ...])
```
The function takes a list of comma-separated arguments, with at least one element. PostgreSQL will evaluate them in order, from left to right, until it finds a non-`NULL` value. This means that arguments to the right of the first non-`NULL` argument are not even evaluated.

Just like any other PostgreSQL function, you can use it in any query clause, including `SELECT`, `WHERE`, `JOIN`, `ORDER BY`, `GROUP BY`, and `HAVING`.

Let’s see it in action in an example:

```
1 SELECT COALESCE(age, 'N/A') AS age
2 FROM users
```
When a record has a `NULL` value in the age column, the `COALESCE` function will replace it with the string 'N/A' in the result set.

## Pros and Cons of COALESCE
Time to dig into the benefits and disadvantages of using the PostgreSQL function.

### Pros

- **Simplifies query logic:** It provides a concise and effective way to handle NULL values. This reduces the need for complex conditional logic and makes your SQL code shorter and more maintainable.
- **Makes it easier to handle NULL values:** Thanks to it, you can deal with NULL values directly in the database. This is generally faster and less cumbersome than doing it at the application level. Plus, different frontends may address missing data in different ways, causing inconsistencies.
- **Flexible with data types:** It supports several data types, including strings and numbers. When required, it automatically performs implicit type conversions. In the case of unsupported data types, the query will fail with `ERROR: COALESCE types and cannot be matched.`

### Cons

- **Limited to the first non-`NULL` value:** This behavior is suitable for scenarios where you only need to replace a single NULL value. If you have to perform operations or substitutions on multiple `NULL` values in a single query, you will need more complex queries.
- **Potential impact on query readability:** As the number of arguments increases or when combined with other conditional logic, the query becomes longer, making it more difficult to read.
- **No control over evaluation order:** Its fixed argument evaluation order represents a limitation in specific scenarios where different logic is required. In such cases, alternative approaches like `CASE` statements may offer more flexibility and control.

## PostgreSQL COALESCE: Use Cases
Let's explore a few real-world scenarios where the function can be applied effectively.

### Handling Missing Values
Suppose you have a customers table and some users did not provide their full names for privacy reasons. You can use the COALESCE function to replace those NULL values with default strings, ensuring that the query produces consistent results.

```
1 SELECT
2    email,
3    COALESCE(full_name, 'Anonymous User') AS full_name
4 FROM
5 customers;
```
<br />
![Note the three "Anonymous User" strings.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/igawj0bkmjcj1b2xkz72.png)
<figure><figcaption>Note the three "Anonymous User" strings.</figcaption></figure>
<br />
In the frontend, you can display these placeholder information fields in place of missing data to improve the user experience.

### Performing Math Operations on NULL Values

Consider a scenario where you have a `products` table storing the price and `discount` percentage. However, some products may not have a discount specified. In this case, the discount column on those records is `NULL`.

Assume you want to calculate the final price after applying the discount. This is the query you may end up writing:

```
1 SELECT
2    name,
3    price,
4    discount,
5    price * (1 - discount) AS final_price
6 FROM
7    products;
```
Let's run it:
<br />
![Note that `final_price` is NULL in three records.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lkbt5v92wealgluxptn3.png)
<figure><figcaption>Note that `final_price` is NULL in three records.</figcaption></figure>
<br />
As you can see, this query produces wrong results. `final_price` should never be `NULL` but it is every time `discount` is `NULL`. This occurs because multiplying a NULL by a number produces a `NULL` in PostgreSQL.

You can easily avoid that and achieve the desired goal by using `COALESCE` as follows:

```
1 SELECT
2    name,
3    price,
4    discount,
5    price * (1 - COALESCE(discount, 0)) AS final_price
6 FROM
7    products;
```
Run the query again:
<br />
![`final_price` now contains the expected data.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4gi0uvwc2bott1qkjwur.png)
<figure><figcaption>`final_price` now contains the expected data.</figcaption></figure>
<br />
Now, when `discount` is `NULL` it gets replaced by the default value 0, ensuring that the calculation proceeds smoothly.

### Handling Multiple Missing Values

Consider an e-commerce platform that manages product inventory. Each product has multiple warehouses, and the stock availability is tracked separately for each warehouse. However, some warehouses may not have reported their stock levels.

To address that, you can use the query below:

```
1 SELECT
2    id,
3    warehouse1_stock,
4    warehouse2_stock,
5    warehouse3_stock,
6    COALESCE(warehouse1_stock, warehouse2_stock, warehouse3_stock) AS available_stock
7 FROM
8    products
9 WHERE
10    id = 5;
```
Note that `COALESCE` is used with three arguments, which are the stock quantities from three different warehouses.

Running the query against a real database would produce the following result set:
<br />
![Note the use of COALESCE with several arguments.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cvt3s083p8nzzgity0z3.png)
<figure><figcaption>Note the use of COALESCE with several arguments.</figcaption></figure>
<br />
Note that `available_stock` has the same value as `warehouse2_stock`. This is because `warehouse1_stock` is NULL. In detail, that query checks each warehouse's stock level and returns the first non-`NULL` value encountered, providing the user with the desired info.

## Conclusion
In this article, you learned that `COALESCE` is a powerful tool for handling NULL values directly in the database. It gives you the ability to replace `NULL` values with placeholder strings, making your queries less complex.

To better appreciate its capabilities, you need a tool that helps you manage databases and visually explore query results. This is where a full-featured database client like DbVisualizer can make all the difference! In addition to being able to connect to dozens of DBMSs, this tool offers advanced query optimization functionality, and full support for all PostgreSQL features, including `COALESCE`. [Download DbVisualizer for free now!](https://www.dbvis.com/download/)

## FAQ
Let’s answer some questions related to the topic of the guide.

### What is the difference between COALESCE and IFNULL in PostgreSQL?
`COALESCE` and `NULLIF` are two PostgreSQL functions to handle `NULL` values. The key difference between them lies in their purpose. The first is used to return the first non-NULL value from a list of arguments. On the other hand, `NULLIF` compares two expressions: NULLIF(expression1, expression2). It returns `NULL` if expression1 and expression2 are equal. Otherwise, it returns expression1. It is typically used to build conditional logic based on equality.

### What are the traditional methods for handling NULL values in PostgreSQL?
Traditionally, developers tended to use techniques `IS NULL` or `IS NOT NULL` conditions to manage NULL values in PostgreSQL. However, these produce long queries that are hard to read and maintain. For this reason, you should prefer conditional statements like the `CASE`, `COALESCE`, `IFNULL`, or `GREATEST` and `LEAST` functions to deal with `NULL` values.

### Can the COALESCE function be used in combination with conditional logic, such as CASE?
Yes, the function can be used in combination with other conditional logic, such as `CASE`. This allows for more complex handling of NULL values based on specific conditions or criteria. By leveraging the flexibility of COALESCE and combining it with conditional logic, you can achieve more sophisticated data transformations and replacements.

### Is COALESCE efficient?
The efficiency of `COALESCE` depends on the size of the dataset and the complexity of the query. In general, it performs well and has a minimal performance impact. However, when dealing with large datasets or is used with a lot of parameters, it can slow down your query.

### How to use COALESCE with a timestamp or date in PostgreSQL?

`COALESCE` can be used with timestamps or dates in PostgreSQL to replace NULL values with strings, provided that you convert the column of type `TIMESTAMP` or `DATE` to string with a cast. Here is an example of how to do it:

```
1 SELECT
2    COALESCE(CAST(birthdate AS TEXT), 'Missing date') AS birthdate,
3    COALESCE(CAST(joined_at AS TEXT), 'Missing timestamp') AS joined_at
4 FROM
5    users;
```

## About the author
Antonello Zanini is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.