jose.soares@sferacarta.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
