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.

Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from

Leave a Reply

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

Notify of

Sort by:   newest | oldest | most voted
8 months 24 days ago

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.

8 months 24 days ago

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 into uktb values(1,2,null);
ERROR: duplicate key value violates unique constraint “prvuk” ….

6 months 13 days ago

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.

5 months 6 days ago

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

Syed Anjum
2 months 2 days ago

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.