Re: BUG #6669: unique index w/ multiple columns and NULLs

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