Introduction to NULL

In the database world, NULL represents unknown or missing information. NULL is not the same as an empty string or the number zero.

Suppose you need to insert the email address of a contact into a table. You can request his or her email address.

However, if you don't know whether the contact has an email address or not, you can insert NULL into the email address column. In this case, NULL indicates that the email address is unknown at the recording time.

NULL is very special. It does not equal anything, even itself. The expressionNULL = NULL returns NULL because it makes sense that two unknown values should not be equal.

To check if a value is NULL or not, you use theIS NULL boolean operator. For example, the following expression returns true if the value in the email address is NULL.

email_address IS NULL

TheIS NOT NULL operator negates the result of theIS NULL operator.

PostgreSQL NOT NULL constraints

To control whether a column can accept NULL, you use theNOT NULL constraint:

CREATE TABLEtable_name(   ...   column_name data_type NOTNULL,   ...);

If a column has aNOT NULL constraint, any attempt toinsert orupdate NULL in the column will result in an error.

Declaring NOT NULL columns

The followingCREATE TABLE statement creates a new table nameinvoices with the not-null constraints.

CREATE TABLE invoices(  id SERIAL PRIMARY KEY,  product_id INT NOT NULL,  qty numeric NOT NULL CHECK(qty > 0),  net_price numeric CHECK(net_price > 0));

This example uses theNOT NULL keywords that follow thedata type of the product_id and qty columns to declareNOT NULL constraints.

Note that a column can have multiple constraints such asNOT NULL,check,unique,foreign key appearing next to each other. The order of the constraints is not important. PostgreSQL may check constraints in any order.

If you useNULL instead ofNOT NULL, the column will accept bothNULL and non-NULL values. If you don't explicitly specifyNULL orNOT NULL, it will acceptNULL by default.

Adding NOT NULL Constraints to existing columns

To add theNOT NULL constraint to a column of an existing table, you use the following form of theALTER TABLE statement:

ALTER TABLE table_nameALTER COLUMN column_nameSET NOT NULL;

To add multipleNOT NULL constraints to multiple columns, you use the following syntax:

ALTER TABLE table_nameALTER COLUMN column_name_1SET NOT NULL,ALTER COLUMN column_name_2SET NOT NULL,...;

Let's take a look at the following example.

First,create a new table called production orders (production_orders):

CREATE TABLE production_orders (idSERIAL PRIMARY KEY,description VARCHAR (40)NOT NULL,material_idVARCHAR (16),qtyNUMERIC,start_date DATE,finish_dateDATE);

Next, insert a new row into theproduction_orders table:

INSERT INTO production_orders (description)VALUES('Make for Infosys inc.');

Then, to make sure that theqty field is not null, you can add the not-null constraint to theqty column. However, the column already contains data. If you try to add the not-null constraint, PostgreSQL will issue an error.

To add theNOT NULL constraint to a column that already contains NULL, you need to updateNULL to non-NULL first, like this:

UPDATE production_ordersSET qty= 1;

The values in theqty column is updated to one. Now, you can add theNOT NULL constraint to theqty column:

ALTER TABLE production_ordersALTER COLUMN qtySET NOT NULL;

After that, you can update the not-null constraints formaterial_id,start_date, andfinish_date columns:

UPDATE production_ordersSET material_id= 'ABC',    start_date = '2015-09-01',    finish_date= '2015-09-01';

Add not-null constraints to multiple columns:

ALTER TABLE production_ordersALTER COLUMN material_idSET NOT NULL,ALTER COLUMNstart_date SET NOT NULL,ALTER COLUMN finish_dateSET NOT NULL;

Finally, attempt to update values in theqty column to NULL:

UPDATE production_ordersSET qty= NULL;

PostgreSQL issued an error message:

[Err] ERROR:null value in column"qty" violatesnot-null constraintDETAIL:  Failingrow contains (1, makefor infosys inc., ABC,null,2015-09-01,2015-09-01).

The special case of NOT NULL constraint

Besides theNOT NULL constraint, you can use aCHECK constraint to force a column to accept not NULL values. TheNOT NULL constraint is equivalent to the followingCHECK constraint:

CHECK(columnIS NOT NULL)

This is useful because sometimes you may want either columna orb is not null, but not both.

For example, you may want eitherusername oremail column of the user tables is not null or empty. In this case, you can use theCHECK constraint as follows:

CREATE TABLE users (  idserial PRIMARY KEY,  usernameVARCHAR (50),  password VARCHAR (50),  emailVARCHAR (50),  CONSTRAINT username_email_notnullCHECK (    NOT (      (        usernameIS NULL        OR username= ''      )      AND (        emailIS NULL        OR email= ''      )    )  ));

The following statement works.

INSERT INTO users (username, email)VALUES('user1',NULL),(NULL,'user2@example.com'),('user2','user2@example.com'),('user3','');

However, the following statement will not work because it violates theCHECK constraint:

INSERT INTO users (username, email)VALUES(NULL,NULL),(NULL,''),('',NULL),('','');

[Err] ERROR:  new rowfor relation"users" violates check constraint"username_email_notnull"

Summary

• Use theNOT NULL constraint for a column to enforce a column not acceptNULL. By default, a column can hold NULL.
• To check if a value isNULL or not, you use theIS NULL operator. TheIS NOT NULL negates the result of theIS NULL.
• Never use equal operator= to compare a value withNULL because it always returnsNULL.

Last updated onJanuary 25, 2024

On this page

• Introduction to NULL
• PostgreSQL NOT NULL constraints
• Declaring NOT NULL columns
• Adding NOT NULL Constraints to existing columns
• The special case of NOT NULL constraint
• Summary