PostgreSQL IS NULL

Updated May 12, 2023

Introduction to PostgreSQL IS NULL

PostgreSQL IS NULL is basically used to check or test the null values in an insert, update, delete, and select queries. We have used null when we want to check that a given value is null or not null; the given condition returns the true value when the given value is null in PostgreSQL. It is used when one or more fields of the table are set as blank.

Syntax:

Below is the working of IS NULL operator in PostgreSQL.

1. IS NULL operator using select statement

Select coumn_name1, column_name2, …, column_nameN from table_name where column_name IS NULL;

2. IS NULL operator using insert statement

Insert into table_name (coumn_name1, column_name2, …, column_nameN) select coumn_name1, column_name2, …, column_nameN from table_name where column_name IS NULL;

3. IS NULL operator using an update statement

Update table_name set column_name = ‘Value of column’ where column_name IS NULL;

4. IS NULL operator using the delete statement

Delete from table_name where column_name IS NULL;

5. IS NULL operator using the expression

Expression (Expressions which was used in our query) IS NULL;

Below is the parameter description syntax of IS NULL operator in PostgreSQL:

• Select: We have used IS NULL operator with the select statement. We retrieved the result from the table using the IS NULL operator and select statement.
• Insert: We have used IS NULL operator with the insert statement. We have inserted the row into the table using IS NULL operator and insert statement.
• Update: We have used the NULL operator with an update statement. We have updated the row into the table using the IS NULL operator and update the statement.
• Delete: We have used the NULL operator with the delete statement. We have ddeleted the row from the table using IS NULL operator and delete statement.
• From: From clause is used to select a specific table to perform operations on the selected table. We have used any table from the database.
• Column name: This is defined as a select column name form table on which we have applied IS NULL operator to perform the operation of select, update, delete and insert.
• Where: This clause selects the specific column from the table to perform the specific operations on a table.
• Expression: We have used any expression with the IS NULL operator in PostgreSQL. Basically, we have used select, update, delete, and insert expression.
• Table name: This is an important parameter of IS NULL operator. We have selected the specific column from the table using the IS NULL operator.

How IS NULL Operator work in PostgreSQL?

Below is the working of IS NULL operator in PostgreSQL.

• IS NULL operator in PostgreSQL is used to check a column’s null values.
• NULL and IS NULL operator works the same in PostgreSQL.
• The example below shows that we use IS NULL operator on the NULL values column.

Code:

\d+ null_test;
SELECT * FROM Null_test WHERE name IS NULL;

Output:

• In the PostgreSQL database, IS null is defined as missing the information from the column we are searching for.
• IS NULL in PostgreSQL is not a value. It is a blank value of the column field.
• IS Null value in the table column field contains the null value.

Code:

SELECT * FROM Null_test

Output:

Examples

Given below are the examples mentioned:

We are using the null_test table to describe the IS NULL operator’s example in PostgreSQL as follows.

Below are the table description and data of the null_test table.

Code:

select * from null_test;
select * from null_test;

Output:

Example #1

IS NULL operator using select statement.

• We have used IS NULL operator with a select statement in PostgreSQL. The below example shows that it IS NULL operator with a select statement.
• In the example below, we used IS NULL operator on the website_url column. After using IS NULL operator on the website_url column will display the records from the null_test table, which was website_url column values are null.

Code:

SELECT * FROM Null_test WHERE website_url IS NULL;

Output:

Example #2

IS NULL operator using insert statement.

• We have used IS NULL operator with insert statement in PostgreSQL. The below example shows that it IS NULL operator with an insert statement.
• In the example below, we used IS NULL operator on the website_url and name column. After using IS NULL operator on the website_url and name column, it will insert the records on all the fields except the website_url and name column.

Code:

INSERT INTO Null_test (company_id, name, address, phone, country) VALUES (21, 'PQR', 'Mumbai', '1234567890', 'India');
INSERT INTO Null_test (company_id, address, phone, country, website_url) VALUES (11, 'Mumbai', '1234567890', 'India', 'www.abc.com');
select * from Null_test;

Output:

Example #3

IS NULL operator using update statement.

• We have used IS NULL operator with an update statement in PostgreSQL. The below example shows that it IS a NULL operator with an update statement.
• In the example below, we used IS NULL operator on the website_url column. After using IS NULL operator in the website_url column, all the rows updated with the name ABCD contain the null value of the website_url column.

Code:

UPDATE Null_test SET name = 'ABCD' WHERE website_url IS NULL;
select * from Null_test;

Output:

Example #4

IS NULL operator using the delete statement.

• We have used IS NULL operator with the delete statement in PostgreSQL. The below example shows that it IS a NULL operator with a delete statement.
• In the example below, we used IS NULL operator on the website_url column. After using IS NULL operator in the website_url column, all the rows deleted contain the null value of the website_url column.

Code

DELETE from null_test where website_url IS NULL;
select * from Null_test;

Output:

We hope that this EDUCBA information on “PostgreSQL IS NULL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

1. PostgreSQL DECODE()
2. UPDATE JOIN PostgreSQL
3. PostgreSQL round
4. PostgreSQL Trunc()