The PostgreSQL NULL is the term used to address a missing worth. A NULL incentive in a table is an incentive in a field that seems, by all accounts, to be clear.
A field with a NULL worth is a field with no worth. It is vital to comprehend that a NULL worth is not quite the same as a zero worth or a field that contains spaces.
Syntax
The fundamental linguistic structure of utilizing NULL while making a table is as per the following −
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
Here, NOT NULL implies that section ought to consistently acknowledge an express estimation of the given information type. There are two sections where we didn't utilize NOT NULL. Consequently, this implies these sections could be NULL.
A field with a NULL worth is one that has been left clear during record creation.
Example
The NULL worth can cause issues while choosing information, since when contrasting an obscure an incentive with some other worth, the outcome is consistently obscure and excluded from the end-product. Think about the accompanying table, COMPANY having the accompanying records −
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0
Allow us to utilize the UPDATE proclamation to set not many nullable qualities as NULL as follows −
testdb=# UPDATE COMPANY SET ADDRESS = NULL, SALARY = NULL where ID IN(6,7);
Presently, COMPANY table ought to have the accompanying records −
id | name | age | address | salary
----+-------+-----+-------------+--------
1 | Paul | 32 | California | 20000
2 | Allen | 25 | Texas | 15000
3 | Teddy | 23 | Norway | 20000
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
6 | Kim | 22 | |
7 | James | 24 | |
(7 rows)
Then, let us see the use of IS NOT NULL administrator to list down all the records where SALARY isn't NULL −
testdb=# SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM COMPANY
WHERE SALARY IS NOT NULL;
The above given PostgreSQL proclamation will create the accompanying outcome −
id | name | age | address | salary
----+-------+-----+------------+--------
1 | Paul | 32 | California | 20000
2 | Allen | 25 | Texas | 15000
3 | Teddy | 23 | Norway | 20000
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
(5 rows)
Coming up next is the utilization of IS NULL administrator which will list down all the records where SALARY is NULL −
testdb=# SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM COMPANY
WHERE SALARY IS NULL;
The above given PostgreSQL articulation will create the accompanying outcome −
id | name | age | address | salary
----+-------+-----+---------+--------
6 | Kim | 22 | |
7 | James | 24 | |
(2 rows)