PostgreSQL: Using NULL and Not NULL clauses
Unknown values of the database table are treated differently by SQL. When one or more fields of a table needs to be set blank then the NULL keyword is used at the time of table creation and NULL value can be used for selecting or inserting or updating data of the table. NULL value does not indicate zero or empty value. It is mainly used for that field where the value is missing or unknown or no value is applicable. But if any field of a table needs to set mandatory then you have to use NOT NULL keyword during the time of table creation. How you can use NULL and NOT NULL clauses in PostgreSQL to run select, insert, update and delete SQL statements are shown in this tutorial.
Create table using NULL and NOT NULL
( item_id SERIAL,
name varchar(40) DEFAULT(‘Not Assign’),
quantity INT NULL,
company_id INT,
PRIMARY KEY (item_id),
FOREIGN KEY (company_id) REFERENCES company(company_id) );
Insert Some Data in tables:
Insert into Company table:
VALUES (1, ‘Samsung’, ‘123….’,‘+337277888’, ‘Korea’, ‘www.samsung.com’);
INSERT INTO company (company_id, name, address, phone, country, website_url)
VALUES (2, ‘Symphony’, ’67/A ….’, ‘+42343567’, ‘Chaina’, ‘www.symphony.com’);
INSERT INTO company (company_id, name, address, phone, country)
VALUES (3, ‘LG’, ’45/B ….’, ”, ‘Japan’);
Insert into items table:
Example-1: Using NULL and NOT NULL in SELECT Query
a) NULL
The following query will retrieve all name and address data from company table where website_url value is null. There is only one record where the website_url value is NULL.
b) NOT NULL
The output of NOT NULL is opposite of NULL. The following select query will return all records from company table where website_url field contains any data.
Example-2: Using NULL or NOT NULL in INSERT Query
The following query will insert company_id value from company table to items table which has no website_url value. There is one record in company where website_url is NULL. So, one record will be inserted after executing the query.
Example-3: Using NULL in UPDATE Query
name field value of items table will be updated which record contains NULL in quantity field. According to the data, one record will be updated after executing the query.
Example-4: Using NULL or NOT NULL in UPDATE Query
The following query will delete records from items table where quantity value is NULL. There is only one record in items table where quantity value is NULL. So, one record will be deleted after executing the query.
You can apply NULL and NOT NULL clauses with WHERE clause for executing any query based on the table if the table contains any NULL value. But NULL value and empty string are not identical. If you create any field of the table without NULL option and keep empty data in that field then NULL clause will not work for that field. You can apply NULL clause for any table based on the table structure.
