PostgreSQL: Composite UNIQUE Constraint does not consider NULL Values

PostgreSQL UNIQUE Constraint does not consider NULL values for uniqueness.
I am surprised when I have found some duplicate values in my database and Unique key constraint already defined in that columns.

I know the thumb rule of UNIQUE Constraint is, UNIQUE Key column can be a NULL Value.

In my Postgres database, I have applied Composite Unique Key in multiple columns and this constraint failed when one of the value is NULL and an other value is NOT NULL.

Below is a full example:

Create sample table with composite Unique Key:

Now insert few records with the same combination:

Postgres Unique Key Null

You can check above result and we can easily find duplicate combination when one column has a NULL value.
This is wrong and if we have this kind of requirement when we need a composite Unique Key, it fails.

The solution is Unique Index:
In this kind of situation, we have to use the Unique Index of PostgreSQL.

Create Unique Index for this example:

When you try to insert duplicate combination after applying this index, you get a below error message.

Anvesh Patel

Leave a Reply

10 Comments on "PostgreSQL: Composite UNIQUE Constraint does not consider NULL Values"

Notify of
avatar
Sort by:   newest | oldest | most voted
PISEY
Guest

Ples check ur ERROR message again , the ERROR message shown you :

ERROR: duplicate key value violates unique constraint “pk_tbl_testuniquenull_id”
DETAIL: Key (id)=(5) already exists.

this is violates unique primary key constraint not composite unique constraint(NoA,NoB,NoC) caused you insert number 5 two times to primary key column, so mean that your Partial Unique Index is not work here.

PISEY
Guest

create table uktb(col1 int,col2 int,col3 int,constraint ukc unique (col1,col2,col3));

create unique index prvuk on uktb (coalesce(col1,-1),coalesce(col2,-1),coalesce(col3,-1));

insert into uktb values(1,2,null);
INSERT 0 1
insert into uktb values(1,2,null);
ERROR: duplicate key value violates unique constraint “prvuk” ….

Mohammaed
Guest

Hi there all, here every person iis shwring these experience, thus it’s good too read this
website, and I used to visit this blog everyday.

Luce
Guest

This website was… how do I say it? Relevant!! Finally I have found something which
helped me. Appreciate it!

Syed Anjum
Guest

The reason of the accepting the values as you said is that null is never equal to another null;
Mathematically null null;
It is because of that the value has been accepted as per my understating.

Note: i made a mistake in posting my previous post. when i forget to make null null.

trackback

guys, this is working find for me, thank you anvesh

trackback

very strange, i have to check in my project. thank you for the information.

wpDiscuz