PostgreSQL: Allow single NULL for UNIQUE Constraint Column

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

In this post, I am sharing a solution on how to allow only single NULL record in the UNIQUE Constraint Column of PostgreSQL.

PostgreSQL: Composite UNIQUE Constraint does not consider NULL Values

Once you define the UNIQUE constraint, you can insert N number of NULL values for that column which is the principal rule of UNIQUE Constraint.

What if I need an only single NULL record for UNIQUE Column, we should check the below demonstration.

Create a sample table with UNIQUE Constraint:

1	CREATE TABLE tbl_testunique (ID INTEGER UNIQUE);

Insert sample multiple NULLs:
You can insert all NULLs.

1 2	INSERT INTO tbl_testunique VALUES (1),(NULL),(NULL),(NULL);

Now, Truncate the table:

1 2	INSERT INTO tbl_testunique VALUES (1),(NULL),(NULL),(NULL);

Create a UNIQUE INDEX with IS NULL:

1 2	CREATE UNIQUE INDEX idx_id ON tbl_testunique ((ID IS NULL)) WHERE ID IS NULL;

Now, Try to execute same INSERT statement:

1 2	INSERT INTO tbl_testunique VALUES (1),(NULL),(NULL),(NULL);

Will get ERROR in the result:

********** Error **********

ERROR: duplicate key value violates unique constraint "idx_id"

SQL state: 23505

Detail: Key ((id IS NULL))=(t) already exists.

Dec 17, 2017Anvesh Patel