PostgreSQL allows duplicate nulls in unique columns

written by paul on January 11th, 2008 @ 11:31 AM

It seems strange, but duplicate null values do not violate unique constraints in PostgreSQL.

Inserting the same non-null value twice in a unique column fails as expected:

# create table test (
  a varchar unique
);

# insert into test values(1);
INSERT 0 1

# insert into test values(1);
ERROR:  duplicate key violates unique constraint "test_a_key"

However, the same is not true for null:

test=# insert into test values(null);
INSERT 0 1

test=# insert into test values(null);
INSERT 0 1

# select * from test;
 a
---
 1

(3 rows)

I think this is misleading, but PostgreSQL says that it is following the SQL standard: Unique Constraints.

Update (1/16/08): Pramod Sadalage showed me that Oracle actually behaves just like PostgreSQL. I’m not sure why I was seeing different behavior, but I could not reproduce the problem.

posted in:

accepting comments

6 comments

Comments

  • david clark on 11 Jan 12:32

    interesting. I've never tried that before but I assumed that nulls wouldn't generate a unique constraint violation. Null means "unknown value" generally or lack of a value. I seem to be more inclined to the postgres implementation.

  • Johnny Kwan on 11 Jan 16:12

    If you are counting on a unique contraint to eliminate duplicate nulls, you are abusing nulls. Nulls are unknown values, and should only be used if something is truly unknown. Like David, I always assumed that unique constraints wouldn't throw out duplicate nulls. I was very surprised the first time that Oracle did. In what context did you discover this? I can think of no legitimate use case that would call for suppressing duplicate nulls.

  • Paul Gross on 12 Jan 00:20

    We only ran into a problem because we use both postgresql and oracle on my current project. We had a test that inserted nulls (in the process of loading test data) and ran against both. The test failed on oracle, but passed on postgresql. We cleaned up the test and the problem went away. I was just surprised that it worked differently on different databases.

  • James Bennett on 15 Jan 13:35

    It's not misleading at all: @NULL != NULL@ in SQL (in fact, *no* value compares equal to @NULL@ in SQL), which means that the @NULL@ being inserted is not equal to the @NULL@ that's already in another row, which means the uniqueness constraint isn't violated.

  • Paul Gross on 15 Jan 21:39

    I guess I find the whole NULL != NULL misleading, since this is not true in other languages. I find it annoying that I have to write queries like "foo is null" instead of "foo = null."

  • Orama on 05 Feb 11:54

    FYI: mysql works same way as postgres in this case

Post a comment

Options:

Size

Colors