From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | jose(dot)soares(at)sferacarta(dot)com |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #6669: unique index w/ multiple columns and NULLs |
Date: | 2012-06-01 03:28:11 |
Message-ID: | 2870.1338521291@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
jose(dot)soares(at)sferacarta(dot)com writes:
> I think I have found an error in pg or at least inconsistency, take a look
> at this.
> I created an unique index on two columns and pg let me enter repeated values
> as NULLs (unknown value),
This is entirely correct per SQL standard: unique constraints do not
reject duplicated rows that include nulls. If you read the standard,
unique constraints are defined in terms of UNIQUE predicates, and a
UNIQUE predicate for a table T is defined thus:
2) If there are no two rows in T such that the value of each column
in one row is non-null and is equal to the value of the cor-
responding column in the other row according to Subclause 8.2,
"<comparison predicate>", then the result of the <unique predi-
cate> is true; otherwise, the result of the <unique predicate>
is false.
(SQL92 section 8.9 <unique predicate>)
This is why a primary key constraint is defined as requiring both UNIQUE
and NOT NULL; you need that to ensure that there are indeed no two
indistinguishable rows.
(Mind you, I'm not here to defend *why* the standard is written that
way. But that is what it says.)
> Oracle don't allows to insert two NULLs in such column.
Oracle is not exactly the most standards-compliant implementation
around. They are well-known to be particularly wrong with respect to
NULLs behavior.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2012-06-01 05:06:46 | Re: BUG #6672: Memory leaks in dumputils.c |
Previous Message | Bruce Momjian | 2012-06-01 01:37:06 | Re: BUG #6666: pg_upgrade 9.2beta1 plpython/plpython2 |