PostgreSQL Duplicate Null Values in Unique Column

Here in this blog, I am going to tell you about some interesting behavior of PostgreSQL database. But before diving deep into it, we should be aware about terminology like Unique Constraint.

Unique Constraint is the concept that ensures that, data stored in a column is always unique, if uniqueness constraint is applied on that column . In short, Unique indexes are the way to avoid data inconsistency and duplication.

So, what is the interesting behavior ?

Consider a scenario, where we have to do unique indexing on a particular column in an already existing table. Also, we are confident that there are no duplicate values present for that column. But we are not sure about null values i.e, it may be possible that we have duplicate null values for that column.

So, what will happen if we do unique indexing on that column, which may have duplicate null values ?

Here comes the BOOM !!!

Answer is, we’ll not get any error when we’ll do unique indexing on that column.

You all must be thinking WHY ?

So, let me tell you one interesting or you can say weird behavior of  PostgreSQL……

Do you know that duplicate null values does not violate uniqueness constraint in PostgreSQL?

Exactly that was my reaction, when I came to know about it. Ideally when we insert same values twice in the column having uniqueness constraint, it should fail. But for null values it doesn’t fail.

  testname=# CREATE TABLE Demo (name varchar unique);
  CREATE TABLE

  testname=# INSERT INTO Demo (name) VALUES ('TestName');
  INSERT 0 1

  testname=# INSERT INTO Demo (name) VALUES ('TestName');
  ERROR:  duplicate key value violates unique constraint "demo_name_key"
  DETAIL:  Key (name)=(TestName) already exists.

In the above example, it is the normal behavior that inserting same value(‘TestName’) twice in the unique column(‘name’) will fail.

  testname=# INSERT INTO Demo (name) VALUES (null);
  INSERT 0 1

  testname=# INSERT INTO Demo (name) VALUES (null);
  INSERT 0 1.

But what is happening here, why it not showing error when we are inserting duplicate value(null) twice in the unique column(‘name’) ?

Yes…. Yes I know this is kind of confusing. But this is what PostgreSQL follows, SQL standards.

According to SQL standard :
“In general, a unique constraint is violated when there are two or more rows in the table where the values of all of the columns included in the constraint are equal. However, two null values are not considered equal in this comparison. That means even in the presence of a unique constraint it is possible to store duplicate rows that contain a null value in at least one of the constrained columns. This behavior conforms to the SQL standard, but we have heard that other SQL databases may not follow this rule. So be careful when developing applications that are intended to be portable.”

For more information like this, stay tuned 🙂

Advertisements