Home 2016 January PostgreSQL: Composite UNIQUE Constraint does not consider NULL Values

PostgreSQL: Composite UNIQUE Constraint does not consider NULL Values

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

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

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

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

Below is a full example on this:

Create a 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. Which 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 the above situation, we have to use the Unique Index of PostgreSQL.

Create a Unique Index for this example:

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

Anvesh Patel
MySQL: Configured InnoDB Engine for Multithreaded DatabaseSQL Server: Remove recent file history from the File Menu
Comments: 8
  1. PISEY
    February 1, 2016 at 8:16 am

    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
      February 1, 2016 at 8:41 am

      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” ….

    • Anvesh Patel
      Anvesh Patel
      February 2, 2016 at 4:30 am

      Thanks Pisey for your observation, I have done some mistake on this demo and as per your suggestion I have updated this article.
      Thank you very much ! šŸ™‚

      • PISEY
        February 12, 2016 at 2:11 am

        you’re welcome, you give me more than i give u , i always follow your demo because i am just a new dba. most of your article is useful for me.

        • Anvesh Patel
          Anvesh Patel
          February 12, 2016 at 4:28 am

          Thank you Very much and Next week on wards, I am going to start RND on NOSQL Technology.
          You will also like this.

  2. Mohammaed
    April 12, 2016 at 3:19 am

    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.

  3. Luce
    May 18, 2016 at 10:21 pm

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

  4. August 23, 2016 at 2:21 am

    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.

Anvesh Patel
Anvesh Patel

Database Engineer

January 18, 2016 PostgreSQL, , , , , , , , , , , , ,