Jan 112008
 

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.

  6 Responses to “PostgreSQL allows duplicate nulls in unique columns”

  1. FYI: mysql works same way as postgres in this case

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

  3. 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.

  4. 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.

  5. 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.

  6. 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.

Sorry, the comment form is closed at this time.