SQL server offers an inbuilt function named ISNULL that is used to replace the NULL values with some specific values. The ISNULL function accepts an expression and a replacement as arguments and replaces the occurrence of a null value with the specified replacement. However, PostgreSQL doesn’t support the ISNULL function. So, how to avail the functionality of the ISNULL function in PostgreSQL?

Through practical examples, this write-up will explain how to replace a NULL value with some specific value in PostgreSQL. To do that, this guide will explain the below-listed concepts:

• SQL ISNULL Function: How Does It Work?
• ISNULL Equivalent in PostgreSQL?
• How to Replace a Null Value With Some Specific Value Using COALESCE() Function?
• How to Replace a Null Value With Some Specific Value Using CASE Expression?

So, let’s get started!

SQL ISNULL Function: How Does It Work?

In SQL, use the following syntax for the ISNULL function:

ISNULL(expression, replacement);

The syntax demonstrates that the ISNULL function accepts two arguments: an expression and a replacement. The ISNULL function will retrieve the specified replacement whenever a NULL value occurs, and it will retrieve the resultant expression for the non-null value.

ISNULL Equivalent in PostgreSQL?

As discussed earlier, PostgreSQL doesn’t support the ISNULL function. So, the question is, does Postgres provide any equivalent of the ISNULL function? Yes! Postgres offers a COALESCE function that can assist us in such scenarios. Moreover, in Postgres, you can use the CASE expression to achieve the same functionality.

How to Replace a Null Value With Some Specific Value Using COALESCE() Function?

The COALESCE function retrieves a first non-null value/argument. You can learn more about the COALESCE function from the following link.

Basic Syntax

You have to follow the below syntax to replace a NULL value with a value of your choice:

COALESCE(expression,replacement);

Example: How to Use COALESCE() Function as Equivalent of ISNULL?

Firstly, let’s utilize the SELECT statement to see the content of the emp_details table:

SELECT * FROM emp_details;

From the output, it can be clearly seen that the emp_bonus column contains some null values. We can replace them with the value of our choice using the COALESCE() function:

SELECT COALESCE(emp_bonus, 0)
FROM emp_details;

Here, emp_bonus represents a column whose NULL values need to be replaced while ‘0’ is the replacement value. So, all in all, the COALESCE() function will find the NULL values in the emp_bonus column and replace all the occurrences of the NULL values with the “0”:

The result set proves the working of COALESCE() function.

How to Replace a Null Value With Some Specific Value Using CASE Expression?

In Postgres, the CASE expression can be used to achieve the functionality of the ISNULL function. Use the below syntax to find and replace the Null values using the CASE expression:

SELECT col_list, CASE 
WHEN expression IS NULL 
THEN replacement_value
ELSE resultant_expression 
END AS column_alias
FROM tab_name;

Example: How to Use CASE Expression as Equivalent of ISNULL?

We will utilize the same table’s column to depict the working of the CASE expression:

SELECT emp_bonus, 
CASE WHEN emp_bonus IS NULL
THEN 0 
ELSE emp_bonus 
END AS modified_emp_bonus
FROM emp_details;

The output authenticates the working of CASE Expression.

Conclusion

SQL server offers an inbuilt function named ISNULL that is used to replace the NULL values with some specific values. However, PostgreSQL doesn’t support the ISNULL function. To achieve the functionality of the ISNULL function in Postgres, the COALESCE() function and CASE expressions are used. Through practical examples, this post explained how to avail the functionality of the ISNULL function using the CASE expression and COALESCE() function.