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

Hi Tom,

Thanks for the explanation about standard sql.
The goodness of it must be accepted by faith. :-)
I still have a doubt about the result of the GROUP BY clause.
It seems to me that there's an inconsistence between the GROUP BY clause
and the unique index.
The GROUP BY clause, consider NULLs as known and equal values
while the index unique constraint consider NULLs as unknown values and
not equals between them.
Don't you think, there's an inconsistence here?

j

Tom Lane wrote:
> 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
>