EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL IS NULL

PostgreSQL IS NULL

By Priya PedamkarPriya Pedamkar

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:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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:

PostgreSQL IS NULL 1

  • 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:

PostgreSQL IS NULL 2

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:

PostgreSQL IS NULL 3

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:

PostgreSQL IS NULL 4

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:

insert statement

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:

using update statement

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:

using delete statement

Recommended Articles

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()
PROGRAMMING LANGUAGES Course
502+ Hours of HD Videos
54 Courses
4 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SELENIUM Certification Course
57+ Hours of HD Videos
15 Courses
9 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
IOT System - Design & Develop an IOT System
65+ Hours of HD Videos
7 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
JENKINS Certification Course
19+ Hours of HD Videos
6 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
POSTGRESQL Certification Course
 17+ Hour of HD Videos
4 Courses
1 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more