PostgreSQL IS NULL

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 have retrieved result from the table by using IS NULL operator and select statement.
• Insert: We have used IS NULL operator with the insert statement. We have insert row into the table by using IS NULL operator and insert statement.
• Update: We have used IS NULL operator with an update statement. We have update the row into the table by using the IS NULL operator and update the statement.
• Delete: We have used IS NULL operator with the delete statement. We have delete the row from the table by 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 use any table from the database.
• Column name: This is defined as a select column name form table on which we have applying IS NULL operator to perform the operation of select, update, delete and insert.
• Where: This clause is used to select 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 select 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 basically used to check the null values of a column.
• NULL and IS NULL operator works the same in PostgreSQL.
• The below example shows that we have 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 defines as missing the information from the column on which we are searching.
• IS NULL in PostgreSQL is not 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 of PostgreSQL IS NULL

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 IS NULL operator with a select statement.
• In the below example, we have used IS NULL operator on the website_url column. After using IS NULL operator on the website_url column, it 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 IS NULL operator with an insert statement.
• In the below example, we have used IS NULL operator on website_url and name column. After using IS NULL operator on website_url and name column, it will insert the records on all the fields except 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 IS NULL operator with an update statement.
• In the below example, we have 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 as 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 delete statement in PostgreSQL. The below example shows that IS NULL operator with delete statement.
• In the below example, we have 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:

This is a guide to PostgreSQL IS NULL. Here we discuss how IS NULL operator work in PostgreSQL with respective query examples. You may also have a look at the following articles to learn more –

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