In Postgres, “NULL” refers to an entry with no value or missing entry. PostgreSQL offers various built-in functions and operators to work with null values, such as COALESCE() function, IS NULL operator, etc. We can use these functions/operators to find and replace the null entries with some default/non-null entries in a Postgres table.

This blog will explain how to find and replace the null entries of a table with some non-null values using the following methods:

• Method 1: Using IS NULL Operator.
• Method 2: Using COALESCE() Function.

So, let’s begin with the COALESCE() function.

Method 1: Using IS NULL Operator

In Postgres, the IS NULL operator allows us to filter out the NULL values, ensuring that our results contain only the relevant data. To replace the null values with some default values, you must use the IS NULL operator with the UPDATE query as follows:

UPDATE table_name
SET column_name = default_value
WHERE column_name IS NULL;

The above-specified query will update the null values of the targeted column with the default value.

Example: How to Replace Null Values With Non-Null Values Using IS NULL Condition?

We have already created a sample table named “staff_info”, whose content is shown in the following snippet:

SELECT * FROM staff_info
ORDER BY employee_id;

The output snippet shows that the “staff_info” table contains null and non-null entries. Suppose we want to update the null values of the employee_salary column with some new values. For this purpose, we will use the IS NULL operator with the UPDATE query as follows:

UPDATE staff_info
SET employee_salary = 15000
WHERE employee_salary IS NULL;

Let’s verify the updated records using the SELECT command:

The null records have been updated/replaced with the default value.

Method 2: Using COALESCE() Function

In Postgres, the COALESCE() function is one of the easiest ways of replacing null values with non-null values of your choice. To replace a null entry with some default value, you need to pass an expression or a column name as the first argument and the default value as the second argument to the COALESCE() function:

COALESCE(expression|col_name, default_val);

Specify a non-null value of your choice in place of the “default_val” argument.

Example: How to Replace Null Values With Default Values Using COALESCE() Function?

The sample table contains the following data:

SELECT * FROM staff_info
ORDER BY employee_id;

Suppose we want to replace the null entries of the “employee_salary” column with a default value of “10000”. For this purpose, we will use the COALESCE() function as follows:

SELECT employee_name, COALESCE(employee_salary, 10000)
FROM staff_info;

The null values have been successfully replaced with “10000”.

That’s all from this Postgres blog!

Conclusion

In PostgreSQL, the COALESCE() function and the IS NULL operator are used to find and replace the null values with some default values. For instance, the IS NULL operator is used with the UPDATE query to find and replace the null entries with some default values. To replace a null entry with some default value using the COALESCE() function, pass an expression and the default value as arguments to the COALESCE() function. This post explained several methods to find and replace the null entries with the default values in PostgreSQL.