How to Use the PostgreSQL Is Null Operator

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

In this article we will discuss as well as demonstrate how to use the PostgreSQL IS NULL operator. We have some recommended prerequisites but you can jump into the article wherever you feel comfortable.

Prerequisites

Before we proceed on discussing how to use the is null operator in PostgreSQL we need to make certain prerequisites.

  • Make sure that there is a PostgreSQL server installed in your local device, execute the terminal command sudo systemctl status postgresql that uses the systemd software suite in a Linux distribution system to view its status.

  • You can also verify the version of the psql shell used for SQL queries in PostgreSQL by executing the command psql -V in the terminal.

Screenshot of the psql status

The null operator

The NULL implies incomplete or not available. A null is not a value, you can’t relate it to a factor like a number or a series of string.

The PostgreSQL is null

In a database management system, they used the condition IS NULL to check and verify a value whether if it has a null value inside their tables in the database.

This IS NULL operator is commonly used in INSERT, DELETE, UPDATE and SELECT statement.

For example, we have a user_tbl in our database with the schema below. We check the schema with this comman \d user_tbl:

1
2
3
4
5
6
7
8
                                        Table "public.user_tbl"
     Column     |          Type          | Collation | Nullable |               Default
----------------+------------------------+-----------+----------+--------------------------------------
 id             | integer                |           | not null | nextval('user_tbl_id_seq'::regclass)
 firstname      | text                   |           |          |
 lastname       | text                   |           |          |
 email          | character varying(100) |           |          |
 contact_number | character varying(20)  |           |          |

We can see that our user_tbl has 4 columns that can have null values because it does not apply a NOT NULL constraint.

And the user_tbl has these values:

1
2
3
4
5
 id | firstname | lastname |        email        |   contact_number
----+-----------+----------+---------------------+--------------------
  1 | Jhon      | Lopez    | lopez@gmail.com     | (+639-26-491-2271)
  2 | Megan     | Mercado  | mm25@gmail.com      |
  3 | Andrew    | Manuel   |                     | (+639-67-314-3127)

Using is null in select statement

Now let us try to use the IS NULLin PostgreSQL SELECT statement:

1
SELECT * FROM user_tbl WHERE contact_number IS NULL;

The output will yield the following single record with ID of 2 Because it is the only record that has a null value in the column contact_number.

1
2
3
 id | firstname | lastname |     email      | contact_number
----+-----------+----------+----------------+----------------
  2 | Megan     | Mercado  | mm25@gmail.com |

Using is null in insert statement

Now let us try to use the IS NULLin PostgreSQL INSERT statement:

1
2
INSERT INTO user_tbl(firstname, lastname, email)
SELECT firstname, lastname, email FROM user_tbl WHERE email IS NULL;

The output will now insert a new record in the table and copies the value because of the SELECT statement included.

1
2
3
4
5
6
 id | firstname | lastname |      email      |   contact_number
----+-----------+----------+-----------------+--------------------
  1 | Jhon      | Lopez    | lopez@gmail.com | (+639-26-491-2271)
  2 | Megan     | Mercado  | mm25@gmail.com  |
  3 | Andrew    | Manuel   |                 | (+639-67-314-3127)
  4 | Andrew    | Manuel   |                 |

Using is null in update statement

Now let us try to use the IS NULLin PostgreSQL UPDATE statement:

1
UPDATE user_tbl SET contact_number = 'Unavailable' WHERE contact_number IS NULL;

The output will now update the null values in the contact_number column:

1
2
3
4
5
6
 id | firstname | lastname |      email      |   contact_number
----+-----------+----------+-----------------+--------------------
  1 | Jhon      | Lopez    | lopez@gmail.com | (+639-26-491-2271)
  3 | Andrew    | Manuel   |                 | (+639-67-314-3127)
  2 | Megan     | Mercado  | mm25@gmail.com  | Unavailable
  4 | Andrew    | Manuel   |                 | Unavailable

Using is null in delete statement

Lastly is to use the IS NULL in PostgreSQL DELETE statement:

1
DELETE FROM user_tbl WHERE email IS NULL;

The output expected would be:

1
2
3
4
 id | firstname | lastname |      email      |   contact_number
----+-----------+----------+-----------------+--------------------
  1 | Jhon      | Lopez    | lopez@gmail.com | (+639-26-491-2271)
  2 | Megan     | Mercado  | mm25@gmail.com  | Unavailable

Conclusion

We hope you understand more about the PostgreSQL IS NULL operator and how to use it. We have demonstrated a few different use cases and hope that we’ve helped you solve your issue.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.