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 this tutorial
DbVisualizer, top rated database management tool and SQL client.
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:
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.
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.
About the author
Antonello Zanini is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.
Top comments (0)