Easy Tutorial
Postgresql Like Postgresql Syntax Postgresql With Postgresql Null Postgresql Lock Postgresql View Postgresql Privileges Postgresql Limit Postgresql And Or Clauses Postgresql Autoincrement Postgresql Expressions Postgresql Data Type Postgresql Constraints Postgresql Where Clause Postgresql Drop Database Postgresql Select Database Windows Install Postgresql Postgresql Select Linux Install Postgresql Postgresql Join Postgresql Index Postgresql Tutorial Postgresql Schema Postgresql Trigger Postgresql Delete Postgresql Update Postgresql Alias Postgresql Union Postgresql Alter Table Postgresql Functions Postgresql Truncate Table Postgresql Group By Postgresql Sub Queries Postgresql Having Postgresql Insert Into Postgresql Create Table Postgresql Order By Postgresql Datetime Postgresql Create Database Postgresql Drop Table Postgresql Operators Postgresql Transaction Postgresql Distinct Mac Install Postgresql
❮ Postgresql With Postgresql Lock ❯

PostgreSQL NULL Values

NULL values represent missing unknown data.

By default, columns in a table can hold NULL values.

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

Syntax

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

Here, NOT NULL indicates that the field must always contain a value. This means that you cannot insert a new record or update a record without adding a value to the field.

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

When querying data, NULL values can cause some issues because comparing an unknown value with any other value always results in an unknown value.

Additionally, you cannot compare NULL with 0 because they are not equivalent.

Example

Example

Create the COMPANY table (download COMPANY SQL file), with the following data content:

tutorialprodb# 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 nullable fields to NULL:

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

tutorialprodb=# 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 where the SALARY value is not empty:

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

The result 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 NULL values.

Here is the usage of the IS NULL operator, listing records where SALARY is NULL:

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

The result is as follows:

id | name  | age | address | salary
----+-------+-----+---------+--------
  6 | Kim   |  22 |         |
  7 | James |  24 |         |
(2 rows)
❮ Postgresql With Postgresql Lock ❯