5.31. PostgreSQL NULL value

NULL The value represents missing unknown data.

By default, the columns of a table can be stored NULL Value.

This chapter explains IS NULL And IS NOT NULL Operator.

5.31.1. Grammar ¶

When creating a table, the basic syntax of NULL 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
);

这里， NOT NULL 表示强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。

Have NULL The field of the value indicates that it can be left blank when the record is created.

When querying data NULL Values can cause problems because an unknown value compares with any other value, and the result is always unknown.

In addition, it is impossible to compare NULL And 0, because they are not equivalent.

5.31.2. Example ¶

Example ¶

Create COMPANY 表（下载 COMPANY SQL 文件 ), the data are as follows:

runoobdb# 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’ll use UPDATE Statement sets several fields that can be set to null to NULL :

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

Now COMPANY The watch looks like this:

runoobdb=# 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 IS NOT NULL Operator puts all SALARY (payroll) the records whose values are not empty are listed:

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

The results are 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 Used to find a NULL The field of the value.

The following is IS NULL Use of operators, listing SALARY (payroll) record with empty value:

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

The results are as follows:

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