Introduction to PostgreSQL IS NULL
PostgreSQL IS NULL is basically used to check or test the null values in insert, update, delete and select queries. We have used is null when we want to check that 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 the one or more fields of the table is 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 update statement
Update table_name set column_name = ‘Value of column’ where column_name IS NULL;
4. IS NULL operator using delete statement
Delete from table_name where column_name IS NULL;
5. IS NULL operator using 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 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 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 update statement. We have update the row into the table by using IS NULL operator and update statement.
- Delete: We have used IS NULL operator with delete statement. We have delete row from the table by using IS NULL operator and delete statement.
- From: From clause is used to select specific table to perform operations on selected table. We have use any table from the database.
- Column name: This is defined as 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 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 important parameter of IS NULL operator. We have select the specific column from the table using 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 column.
- NULL and IS NULL operator works same in PostgreSQL.
- Below example shows that we have use IS NULL operator on NULL values column.
Code:
\d+ null_test;
SELECT * FROM Null_test WHERE name IS NULL;
Output:
- In 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 blank value of column field.
- IS Null value in 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 null_test table to describe example of IS NULL operator in PostgreSQL as follows.
Below is the table description and data of 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 select statement in PostgreSQL. Below example shows that IS NULL operator with select statement.
- In below example we have used IS NULL operator on website_url column. After using IS NULL operator on website_url column it will display the records from 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. Below example shows that IS NULL operator with insert statement.
- In 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 update statement in PostgreSQL. Below example shows that IS NULL operator with update statement.
- In below example we have used IS NULL operator on website_url column. After using IS NULL operator in website_url column all the rows updated with name as ABCD which contains null value of 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 delete statement.
- We have used IS NULL operator with delete statement in PostgreSQL. Below example shows that IS NULL operator with delete statement.
- In below example we have used IS NULL operator on website_url column. After using IS NULL operator in website_url column all the rows deleted which contains null value of website_url column.
Code
DELETE from null_test where website_url IS NULL;
select * from Null_test;
Output:
Recommended Articles
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 –
