COALESCE in PostgreSQL

What is the COALESCE Function in PostgreSQL?

The COALESCE function in PostgreSQL is designed to evaluate a list of expressions and return the first non-NULL value from the list. Its primary purpose is to handle situations where a value might be NULL and replace it with a meaningful alternative. The function takes any number of arguments, such as columns, literals, or even other functions, and returns the first non-NULL value encountered in the list.

The syntax of the COALESCE function in PostgreSQL is straightforward, and we'll explore it in the next section.

PostgreSQL COALESCE Function Syntax

The syntax of the COALESCE function is as follows:

Here, expr1, expr2, and so on are the expressions or values that we want to evaluate. PostgreSQL will check each expression in order and return the first non-NULL value. If all the expressions evaluate as NULL, the COALESCE function will also return NULL.

Let's move on to an example to better understand the syntax and behavior of the COALESCE function in PostgreSQL.

PostgreSQL COALESCE Example

Suppose we create a table named "students" with the following SQL query.

This query creates a table called "students" with four columns: "id", "name", "age", and "city". The "id" column is defined as SERIAL, which will automatically generate a unique value for each new row. The "name" column is defined as VARCHAR(100) and is set as NOT NULL, meaning it cannot contain NULL values. The "age" and "city" columns allow NULL values.

Next, we insert the data into the "students" table using the following SQL queries.

These queries will insert the provided data into the "students" table, creating three rows with the specified values for each column. The "id" column will automatically generate unique values due to the SERIAL data type.

The final data in the "students" table looks as follows:

Now, let's say we want to retrieve the age and city of the students, but if the age or city is NULL, we want to display a default value, such as "Not specified". We can achieve this using the COALESCE function as follows:

Output:

The result of the query will be:

In this example, the COALESCE function evaluated the age and city columns and replaced the NULL values with the default value "Not specified". As a result, our query provides a more informative and user-friendly output.

Preventing NULL Values with COALESCE

One of the main advantages of the COALESCE function is its ability to prevent NULL-related issues in queries. NULL values can lead to unexpected results and errors when performing calculations or comparisons. Using COALESCE to replace NULL values with meaningful alternatives ensures that our queries produce consistent and reliable outputs.

For example, consider a scenario where we have a table named "student_scores" which stores the scores of students obtained in different subjects. The SQL query to create this table looks as follows:

Next, let us insert data into the "student_scores" table using the following set of SQL queries.

Now, we have the "student_scores" table with data:

We want to calculate the total score of students but some of the scores are missing (i.e. having a NULL value). We can use COALESCE to treat the NULL scores as zero and correctly calculate the total:

Output:

Even if some of the scores are NULL, this query will handle them gracefully by treating them as zeros and providing an accurate total score for each student.