intro
Let’s figure out whether or not the ISNULL PostgreSQL function exists, why, and explore its alternatives to get the same functionality.
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:
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:
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 AND
, OR
, 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:
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:
1
CASE WHEN a IS NULL THEN b ELSE a END
PostgreSQL example:
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:
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:
1
SELECT name, price, COALESCE(stock_level, 0) AS stock_level
2
FROM products;
Similarly, COALESCE
also returns the non-NULL expression.
Another way to achieve the same result is with a CASE
statement:
1
SELECT name, price, CASE WHEN stock_level IS NULL THEN 0 ELSE
2
stock_level END AS stock_level
3
FROM products;
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:
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:
1
SELECT name, stock_level IS NULL AS unknown_status
2
FROM products;
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 CASE
. Download 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.