NULL
POSTGRESQL

PostgreSQL ISNULL: The Missing Function

intro

Let’s figure out whether or not the ISNULL PostgreSQL function exists, why, and explore its alternatives to get the same functionality.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT
PostgreSQL logo POSTGRESQL
THE POSTGRESQL DATABASE

Handling NULL values in the backend and frontend is cumbersome. Noone wants to fill their code with null checks – the database should perform such actions instead. Here is why most popular DBMSs provide features such as ISNULL() to handle them directly within SQL queries. However, not all of these functions and operators are standard SQL.

So, is ISNULL part of PostgreSQL? In this article, you will find it out and explore a PostgreSQL ISNULL example list.

Is There a PostgreSQL ISNULL function?

Even though ISNULL() is a function present in some SQL dialects, including T-SQL and MySQL, it is not part of the SQL language standard. This means that not all DBMSs support it. In particular, PostgreSQL does not have a built-in ISNULL function.

Since the function is not part of the standard SQL specification, its implementation and purpose change from DBMS to DBMS. Let’s now explore how it differs in T-SQL and MySQL.

In Microsoft SQL Server (T-SQL), the ISNULL(a, b) function returns b when a is NULL. In other words, it replaces a NULL with a specified replacement value. For example:

Copy
        
1 SELECT ISNULL(salary, 0) AS salary 2 FROM employee;

This function helps avoid logical inconsistencies and comes in handy when dealing with NULL handling.

In MySQL, ISNULL(expr) performs a logical test on the input parameter. It returns 1 if expr is NULL, or 0 otherwise. Thus, it takes a single argument and checks its value for NULL. For example:

Copy
        
1 SELECT name, surname 2 FROM employee 3 WHERE ISNULL(middle_name);

This function shares special behaviors with IS NULL and is generally used in conjunction with logical or conditional operators, such as ANDOR, or IF.

What Is the PostgreSQL Equivalent for ISNULL?

There are different PostgreSQL approaches that you can follow to achieve goals similar to those provided by ISNULL. These depend on the implementation of the function.

In PostgreSQL, the most common equivalent of the SQL Server function is COALESCE. The main difference between the two is that the first expects only two arguments, while the latter accepts n input parameters.

Thus, the syntax of COALESCE is: COALESCE(a, b [, c ... ] ). The function returns the first non-NULL value, from left to right.

Equivalent PostgreSQL ISNULL example:

Copy
        
1 SELECT COALESCE(salary, 0) AS salary 2 FROM employee;

Keep in mind that COALESCE is part of the standard SQL specification. That means that it is not only available in PostgreSQL but it is also present in all other SQL dialects, including T-SQL itself. Check out our in-depth guide on COALESCE to learn more about it.

Another T-SQL's ISNULL equivalent in PostgreSQL is the CASE operator. Thanks to the CASE statement, you can implement IF/ELSE behavior. When used in combination with the IS NULL operator, this enables you to achieve the same functionality as ISNULL(a, b) in T-SQL:

Copy
        
1 CASE WHEN a IS NULL THEN b ELSE a END

PostgreSQL example:

Copy
        
1 SELECT 2 CASE WHEN salary IS NULL THEN 0 ELSE salary END AS salary 3 FROM employee;

When it comes to the MySQL meaning of ISNULL, the easiest way to get the same behavior is through IS NULL. The main difference is that the function in MySQL returns an integer, while IS NULL in PostgreSQL returns a boolean. At the same time, MySQL does not support the boolean data type, so the two expressions can be considered equivalent most of the time.

Equivalent PostgreSQL ISNULL Example List

To better understand how to replace ISNULL in PostgreSQL, let’s take a look at some examples. To visually explore the results of example queries, we will adopt DbVisualizer. This database client supports more than 50 languages and represents the perfect tool for connecting and comparing different database technologies.

Example 1: Replace NULLs with a default value

In T-SQL, ISNULL is mainly used to replace NULL values with placeholders. Dealing with NULL values in the backend or frontend can be time-consuming. Here is why addressing those values directly at the database layer makes everything easier and faster.

Consider the example below:

Copy
        
1 SELECT product_name, price, ISNULL(stock_level, 0) AS stock_level 2 FROM products;

This query returns the value of stock_level if it is not NULL, or 0 otherwise.

The PostgreSQL ISNULL example equivalent for that query is:

Copy
        
1 SELECT name, price, COALESCE(stock_level, 0) AS stock_level 2 FROM products;

Similarly, COALESCE also returns the non-NULL expression.

Note the 0s in the stock_level column.
Note the 0s in the "stock_level" column.

Another way to achieve the same result is with a CASE statement:

Copy
        
1 SELECT name, price, CASE WHEN stock_level IS NULL THEN 0 ELSE 2 stock_level END AS stock_level 3 FROM products;
Executing the equivalent query in DbVisualizer.
Executing the equivalent query in DbVisualizer.

Note that the result sets in the two images are the same.

Example 2: Better read NULL values

ISNULL() in MySQL can be adopted to make it easier to check whether a column is NULL or not. Take a look at the example below:

Copy
        
1 SELECT name, ISNULL(stock_level) AS unknown_status 2 FROM products;

In this query, unknown_status will contain 1 if stock_level is NULL, and 0 otherwise. This SELECT-generated column makes it easier to read the results and keep track of the stock data status.

The PostgreSQL ISNULL example alternative is:

Copy
        
1 SELECT name, stock_level IS NULL AS unknown_status 2 FROM products;
Note the true and false values in the results.
Note the "true" and "false" values in the results.

Similarly, the IS NULL operator will return a boolean representing the status of the stock level associated with each product.

Congrats! The absence of ISNULL in PostgreSQL is no longer a problem!

Conclusion

In this guide, you learned that ISNULL is a non-standard feature that not all SQL dialects support. Although PostgreSQL does not have it, it is pretty simple to achieve equivalent functionality.

Using a tool that can connect to multiple databases simultaneously makes it way easier to write queries with the equivalent result. A full-featured database client like DbVisualizer makes all the difference here! In addition to connecting to dozens of DBMSs, this tool offers advanced query optimization functionality, and full support for all PostgreSQL features, including COALESCE and CASEDownload DbVisualizer for free now!

FAQ

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

What is the difference between ISNULL and COALESCE?

ISNULL is a function specific to the SQL dialect, while COALESCE is a standard SQL function. The former behaves differently depending on the specific implementation. The second always returns the first non-NULL value, regardless of the database system in use.

Why doesn't PostgreSQL support ISNULL?

ISNULL in PostgreSQL does not exist. The reason is that the function is not part of the SQL standard, and PostgreSQL is not required to provide an implementation for it.

Why do MySQL and SQL Server have the ISNULL function and PostgreSQL does not?

MySQL and SQL Server are not standards-compliant databases. They both have their own proprietary extensions and aspire to provide rich features to users. On the other hand, PostgreSQL is pretty much a standards-compliant database. The standard SQL language does not include the ISNULL function, so PostgreSQL skipped it.

What is the difference between using IS NULL and = NULL in PostgreSQL?

IS NULL in PostgreSQL returns true when the value being checked is NULL. Instead, using = NULL will result in NULL because NULL is not equal to anything, not even itself. So, the first operator checks for nullity, while the second is often used by mistake.

What is the difference between IS NULL and IS NOT NULL in PostgreSQL?

IS NULL is used in PostgreSQL to check if a value is NULL, while IS NOT NULL is used to check if a value is not NULL. These operators are commonly used in WHERE clauses or in conditions to filter or evaluate NULL values in the database.

Dbvis download link img
About the author
Antonello Zanini

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

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

Adding Dates in SQL: A Complete Tutorial

author Antonello Zanini tags DATE DATETIME MySQL POSTGRESQL SQL SERVER 7 min 2024-04-15
title

Glossary of the SQL Commands You Need to Know

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 12 min 2024-04-11
title

SQL Add to Date Operations: A Complete Guide

author Antonello Zanini tags DATE DATETIME MySQL ORACLE POSTGRESQL SQL SQL SERVER 6 min 2024-04-01
title

SQL CAST Function: Everything You Need to Know

author Antonello Zanini tags CAST MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-03-28
title

PostgreSQL Upsert: INSERT ON CONFLICT Guide

author Antonello Zanini tags POSTGRESQL UPSERT 7 min 2024-03-25
title

Postgres TEXT vs VARCHAR: Comparing String Data Types

author Antonello Zanini tags POSTGRESQL TEXT VARCHAR 6 min 2024-03-14
title

Schemas in PostgreSQL

author Leslie S. Gyamfi tags POSTGRESQL SCHEMA 6 min 2024-03-11
title

5 Ways to Split a String in PostgreSQL

author Antonello Zanini tags POSTGRESQL Split 6 min 2024-02-29
title

PostgreSQL CASE: A Comprehensive Guide

author Leslie S. Gyamfi tags CASE POSTGRESQL 7 min 2024-02-22
title

Discover All PostgreSQL Data Types

author Antonello Zanini tags DATA TYPES POSTGRESQL 12 min 2024-02-05

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗