PostgreSQL NULL Values

A NULL value represents missing unknown data.

By default, a table column can hold NULL values.

This chapter explains the IS NULL and IS NOT NULL operators.

Syntax

The basic syntax of NULL when creating a table is as follows:

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 indicates that the field is enforced to always contain a value. This means that you cannot insert a new record or update a record without adding a value to this field.

A field with a NULL value can be left blank when creating a record.

When querying data, NULL values can cause some problems because an unknown value compared with any other value will always result in unknown.

Also, you cannot compare NULL and 0, because they are not equivalent.

Example

Example

Create the COMPANY table (Download the COMPANY SQL file), the data content is as follows:

exampledb# select * from COMPANY;
 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 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Next, we use the UPDATE statement to set several fields that can be set to empty to NULL:

exampledb=# UPDATE COMPANY SET ADDRESS = NULL, SALARY = NULL where ID IN(6,7);

Now the COMPANY table looks like this:

exampledb=# select * from company;
 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)

IS NOT NULL

Now, we use the IS NOT NULL operator to list all records whose SALARY (salary) value is not NULL:

exampledb=# SELECT  ID, NAME, AGE, ADDRESS, SALARY FROM COMPANY WHERE SALARY IS NOT NULL;

The result obtained is as follows:

 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)

IS NULL

IS NULL is used to find fields with a NULL value.

Below is the usage of the IS NULL operator to list the records where the SALARY (salary) value is NULL:

exampledb=#  SELECT  ID, NAME, AGE, ADDRESS, SALARY FROM COMPANY WHERE SALARY IS NULL;

The result obtained is as follows:

id | name  | age | address | salary
----+-------+-----+---------+--------
  6 | Kim   |  22 |         |
  7 | James |  24 |         |
(2 rows)

Other Extensions