2 Easy ways to handle the ISNULL function in PostgreSQL

SELECT queries are frequently used by developers to retrieve the necessary data from PostgreSQL databases. There is a chance that some of the columns’ data is missing. Any other database, including PostgreSQL, uses a NULL value to indicate missing or undefined values. In this post, I have explained how to use the ISNULL function in PostgreSQL.

If there are NULL values in the table, we must replace the NULL values in the data using select queries in order to prevent the NULL value from showing up in the application’s user interface.

In order to meet this requirement, ISNULL functions are available in MySQL and MS SQL Server to determine whether a given value is NULL or not. If the value is NULL, change it to a different value.

There are two alternative methods available in PostgreSQL to verify null values instead of relying on the ISNULL function. The following techniques are used:

1. COALESCE
2. SQL CASE

COALESCE function to check ISNULL condition

Similar to how the ISNULL function operates, the COALESCE function also accepts a replacement value as an argument, just like the ISNULL function does.

COALESCE function Syntax

Below is the syntax of the COALESCE function.

COALESCE(<value>, <replacement_value>)

In the <value>, we can place a column name on which you want to check the NULL value, and in the replacement_value, we can provide the value that comes in the results only when the <value> argument is null.

COALESCE example

Let’s take an illustration: we have an employee table with an age field that has NULL values in several of its rows.

As shown in the above screenshot, the age column starts with 6 rows and is NULL. Now, as per some requirements, we need to make a SELECT query to get age data, and age data should come in proper format with no null values. If NULL is present, replace the age data with a -1 value.

To do this, we are using the COALESCE function, as shown below.

In the above screenshot, we have used the COALESCE function with -1 as a second parameter. If age is NULL, then PostgreSQL replaces age data with a -1 value.

To read more about the COALESCE function, read the official documentation here.

SQL Case to use ISNULL function in PostgreSQL

Since the ISNULL function in PostgreSQL is not available, we may use the SQL Case to verify NULL values in columns and, if the value is NULL, replace it with a new value. A SQL case is a condition statement, similar to if-else expressions in other programming languages.

CASE Syntax

CASE WHEN <condition> THEN result
	[ELSE result]
END

In the above syntax, we can replace <condition> with our NULL check and result with our replacement value. If the value is not NULL, then we can use the ELSE statement to return the original value.

CASE Example

In this example, we will again use the employee table. We issue a SELECT statement for the age column. If the age column value is NULL, then we replace the NULL value with 0.

SELECT name, salary, 
	CASE 
		WHEN age IS NULL THEN 0
		ELSE age END 
FROM employee

In the above screenshot, when we have missing age data, the data is coming in as 0.

Summary

PostgreSQL doesn’t have an ISNULL function. To replace the ISNULL function, PostgreSQL has two alternatives, like COALESCE and SQL Case, for identifying and replacing NULL values. In this post, examples are provided of how to use COALESCE and Case for handling NULL value scenarios.

Read More:

• SELECT first row in each group in PostgreSQL
• An easy way to describe table in psql