PostgreSQL NOT NULL Constraint

Summary: in this tutorial, you’ll learn about NULL and how to use the PostgreSQL NOT NULL constraint to ensure a column will not have NULL.

Introduction to NULL

NULL represents the absence of a value, indicating that the data is unknown or missing.

For example, if you want to record warehouse information but don’t know its address at the time of recording, you can put NULL into the address column. Later, you can update NULL in the address column with the correct address.

Note that NULL means an unknown value. It is different from an empty string ('') or zero (0), which are the defined values.

PostgreSQL uses three-valued logic for NULL. This means that if you compare NULL with any value, the comparison returns NULL.

For example, comparing NULL with a number will result in NULL:

SELECT NULL = 1 AS result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 result
--------
 NULLCode language: plaintext (plaintext)

Even NULL is not equal to NULL; comparing NULL with NULL also results in NULL:

SELECT NULL = NULL AS result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

This result makes sense because comparing an unknown value with another value will result in an unknown result.

Inserting NULL into a column

When you define a column for a table, that column is nullable. It means that you can insert NULL into the column.

For example, the following creates a table called warehouses:

CREATE TABLE warehouses (
  warehouse_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(255),
  address VARCHAR(255)
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The data types of name and address columns are VARCHAR, but they can accept NULL.

The following INSERT statement inserts a new row into the warehouses table with NULL in the address column:

INSERT INTO warehouses(name, address)
VALUES('San Jose', NULL)
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 warehouse_id |   name   | address
--------------+----------+---------
            1 | San Jose | NULLCode language: plaintext (plaintext)

If you omit a column in the INSERT statement, PostgreSQL will use NULL as the default value. For example:

INSERT INTO warehouses (name)
VALUES ('San Francisco') 
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Using PostgreSQL NOT NULL constraint

To ensure that a column cannot have NULL, you use the NOT NULL constraint.

Here’s the syntax for defining a NOT NULL constraint for a column:

column_name data_type NOT NULLCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

After defining a NOT NULL constraint, the column will not accept NULL. If you attempt to do so, PostgreSQL will issue an error.

Note that primary key columns implicitly have a NOT NULL constraint.

The NOT NULL constraint is crucial for maintaining data integrity and consistency across your database.

By enforcing a NOT NULL constraint, you can ensure that essential columns such as email addresses always have value. The NOT NULL constraints prevent situations where NULL could lead to errors in data processing.

The NOT NULL constraint simplifies data management because you won’t need to write additional logic to handle NULL, making your SQL statements simpler and more efficient.

The NOT NULL constraint also helps enforce business rules at the database level, ensuring your app does not operate with unknown values.

PostgreSQL NOT NULL constraint example

First, drop the warehouses table:

DROP TABLE IF EXISTS warehouses;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, recreate the warehouses table whose name and address columns have the NOT NULL constraints:

CREATE TABLE warehouses (
  warehouse_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  address VARCHAR(255) NOT NULL
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Third, attempt to insert NULL into the address column:

INSERT INTO warehouses (name, address)
VALUES ('San Meto', NULL) 
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Error:

ERROR: null value in column "address" of relation "warehouses" violates not-null constraintCode language: plaintext (plaintext)

PostgreSQL issued a not-null constraint violation and rejected the insert.

Finally, insert a new row into the warehouses table:

INSERT INTO warehouses (name, address)
VALUES ('San Meto',' 1st Ave Suite K03, San Mateo, CA 94403') 
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 warehouse_id |   name   |                 address
--------------+----------+-----------------------------------------
            2 | San Meto |  1st Ave Suite K03, San Mateo, CA 94403Code language: plaintext (plaintext)

Summary

• NULL indicates the absence of value.
• The comparison involving NULL will result in NULL.
• The table column accepts NULL by default.
• Use the NOT NULL constraint to ensure that the column does not accept NULL.