Several popular relational database management systems (RDBMSs) offer distinct approaches to deal with NULL values via their built-in ISNULL() functions. Oracle database, for example, implements ISNULL() to simply check if given expression is NULL; SQL Server implements ISNULL() to replace NULL values with a given value.
PostgreSQL, on the other hand, does not have native ISNULL() support, but it provides several alternatives to achieve similar functionalities.
In this blog, I will explain possible alternatives to achieve ISNULL() for both checking and replacing NULL values in PostgreSQL 16 with examples. So, let’s get started.
IsNull PostgreSQL Example Table
We will use this PostgreSQL table as the base in subsequent examples.
CREATE TABLE userinfo (userid INT, username TEXT, age INT, salary INT);
INSERT INTO userinfo VALUES( 1, 'hken', 25, 60000);
INSERT INTO userinfo VALUES( 2, 'hcary', NULL, 30000);
INSERT INTO userinfo VALUES( 3, 'zdavid', 30, NULL);
INSERT INTO userinfo VALUES( 4, 'ctom', NULL, NULL);
INSERT INTO userinfo VALUES( 5, NULL, 20, 45000);
SELECT * FROM userinfo;
userid | username | age | salary
--------+----------+-----+--------
1 | hken | 25 | 60000
2 | hcary | | 30000
3 | zdavid | 30 |
4 | ctom | |
5 | | 20 | 45000
(5 rows)
ISNULL – NULL Value Checking
Since there is no native ISNULL() function available in PostgreSQL, there is several alternative options to to check if a value is NULL in your query.
IS NULL Predicate
IS NULL and IS NOT NULL Predicates are parts of the SQL standard syntax supported in many major RMDBMSs. They can be used in conjunction with SELECT, INSERT, UPDATE, and DELETE clauses to fine tune your query.
SELECT * FROM userinfo WHERE age IS NULL;
userid | username | age | salary
--------+----------+-----+--------
2 | hcary | | 30000
4 | ctom | |
(2 rows)
SELECT * FROM userinfo WHERE salary IS NOT NULL;
userid | username | age | salary
--------+----------+-----+--------
1 | hken | 25 | 60000
2 | hcary | | 30000
5 | | 20 | 45000
(3 rows)
Non-Standard ISNULL Predicate
PostgreSQL also supports ISNULL and NOTNULL predicates that are not parts of SQL standard syntax. They have the same effects as IS NULL and IS NOT NULL.
SELECT * FROM userinfo WHERE age ISNULL;
userid | username | age | salary
--------+----------+-----+--------
2 | hcary | | 30000
4 | ctom | |
(2 rows)
SELECT * FROM userinfo WHERE salary NOTNULL;
userid | username | age | salary
--------+----------+-----+--------
1 | hken | 25 | 60000
2 | hcary | | 30000
5 | | 20 | 45000
(3 rows)
IS DISTINCT FROM NULL Predicate
IS DISTINCT FROM and IS NOT DISTINCT FROM predicates are also parts of the SQL standard syntax that can be used to test NULL values:
IS NOT DISTINCT FROM NULLis equivalent toIS NULLIS DISTINCT FROM NULLis equivalent toIS NOT NULL
SELECT * FROM userinfo WHERE age IS NOT DISTINCT FROM NULL;
userid | username | age | salary
--------+----------+-----+--------
2 | hcary | | 30000
4 | ctom | |
(2 rows)
SELECT * FROM userinfo WHERE salary IS DISTINCT FROM NULL;
userid | username | age | salary
--------+----------+-----+--------
1 | hken | 25 | 60000
2 | hcary | | 30000
5 | | 20 | 45000
(3 rows)
ISNULL – Replace NULLs With Default Values
Since there is no native ISNULL() function available in PostgreSQL, there is several alternatives to check and replace NULLs with default values in a query.
COALESCE Function
COALESCE() function can be used to substitute NULL values with user-supplied values or expressions and it can be used with SELECT, UPDATE and INSERT clauses. This function has this syntax according to official PostgreSQL documentation:
COALESCE(value [, ...])
It takes 2 or more values or expressions and will return the first non-NULL value in the function argument. If all of the arguments evaluate to NULL, COALESCE() will return NULL. The following example will return all rows in userinfo table and perform the following replacements:
- replace NULL username with the value ‘unnamed’.
- replace NULL age with the value 18.
- replace NULL salary with 25000.
Please note that the array of values’ data type must be the same as the first argument
SELECT userid,
COALESCE(username, 'unnamed') AS username,
COALESCE(age, 18) AS age,
COALESCE(salary,25000) AS salary
from userinfo;
userid | username | age | salary
--------+----------+-----+--------
1 | hken | 25 | 60000
2 | hcary | 18 | 30000
3 | zdavid | 30 | 25000
4 | ctom | 18 | 25000
5 | unnamed | 20 | 45000
(5 rows)
CASE Expression
CASE is a generic conditional expression similar to if/else statement in programming languages. It evaluates a given condition and returns an appropriate result. This conditional expression has this syntax according to official PostgreSQL documentation:
CASE WHEN condition THEN result
[WHEN ...]
[ELSE result]
END
If ELSE clause if omitted, and no condition evaluates to true, the end result will be NULL. The following example does the same thing as the COALESCE() example above.
SELECT userid,
CASE WHEN username IS NULL THEN 'unnamed' ELSE username END AS username,
CASE WHEN age IS NULL THEN 18 ELSE age END AS age,
CASE WHEN salary IS NULL THEN 25000 ELSE salary END AS salary
from userinfo;
userid | username | age | salary
--------+----------+-----+--------
1 | hken | 25 | 60000
2 | hcary | 18 | 30000
3 | zdavid | 30 | 25000
4 | ctom | 18 | 25000
5 | unnamed | 20 | 45000
(5 rows)
Create Your Own ISNULL() In PostgreSQL
Since we know the alternatives in PostgreSQL to achieve ISNULL() as both NULL value checker and replacer, it is possible to create our own ISNULL() function based on PL/PGSQL inside PostgreSQL, similar to the one supported in Oracle and SQL Server.
Create ISNULL in PostgreSQL as NULL Value Checker
CREATE OR REPLACE FUNCTION isnull(expression ANYELEMENT)
RETURNS INTEGER AS $$
BEGIN
IF expression IS NULL THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END;
$$ LANGUAGE plpgsql;
isnull postgresql example:
SELECT isnull(username) AS username FROM userinfo;
username
----------
0
0
0
0
1
(5 rows)
Create ISNULL in PostgreSQL as NULL Value Replacer
CREATE OR REPLACE FUNCTION isnull(expr1 ANYELEMENT, expr2 ANYELEMENT) RETURNS ANYELEMENT AS $$ BEGIN RETURN COALESCE(expr1, expr2); END; $$ LANGUAGE plpgsql;
isnull postgresql example:
SELECT isnull(username, 'unnamed') AS username FROM userinfo; username ---------- hken hcary zdavid ctom unnamed (5 rows)
Summary
PostgreSQL does not have built-in ISNULL() function like Oracle and SQL Server do, but with several alternatives options provided by PostgreSQL, we can easily make our own version of ISNULL() function and achieve the same functionalities with the help of CREATE FUNCTION. feature.
Reference
- PostgreSQL: Documentation: 16: 9.2. Comparison Functions and Operators
- PostgreSQL: Documentation: 16: 9.18. Conditional Expressions
- SQL ISNULL() Explained
- How to use COALESCE in PostgreSQL | EDB
- PostgreSQL ISNULL Function With Examples – CommandPrompt Inc.
Hi, this is Cary, your friendly tech enthusiast, educator and author. I enjoy simplifying complex concepts, diving into coding challenges, unraveling the mysteries of software and most importantly, sharing and teaching others about all things tech.