How to Use the PostgreSQL Is Null Operator
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 commandpsql -V
in the terminal.
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 NULL
in 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 NULL
in 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 NULL
in 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