Skip to content
Home » All Posts » Achieve ISNULL Function in PostgreSQL 16 with Right Examples

Achieve ISNULL Function in PostgreSQL 16 with Right Examples

isnull postgresql example

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 NULL is equivalent to IS NULL
  • IS DISTINCT FROM NULL is equivalent to IS 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

Tags:

Join the conversation

Your email address will not be published. Required fields are marked *