Re: unique constraint - bug? - Mailing list pgsql-general

From Tom Lane
Subject Re: unique constraint - bug?
Date
Msg-id 24455.964103107@sss.pgh.pa.us
Whole thread Raw
In response to Re: unique constraint - bug?  ("Oliver Elphick" <olly@lfix.co.uk>)
Responses Re: unique constraint - bug?  (mikeo <mikeo@spectrumtelecorp.com>)
List pgsql-general
"Oliver Elphick" <olly@lfix.co.uk> writes:
> Tom Lane wrote:
>> Two nulls are never considered equal, therefore the unique constraint
>> does not trigger.

> I think you are not interpreting this right:

>         A unique constraint is satisfied if and only if no two rows in
>         a table have the same non-null values in the unique columns.

> I think it means that nulls are to be left out of account in the
> comparison.

Hmm.  What then of

        a, b, c
        a, NULL, c

If I "ignore the null" then these two rows are equal as well.

Still, you're right that it's a little more ambiguously worded than
I thought.  Can anyone check how other DBMSs handle this?

            regards, tom lane

pgsql-general by date:

Previous
From: Andreas Tille
Date:
Subject: Re: Migrating from MS SQL server
Next
From: mikeo
Date:
Subject: Re: unique constraint - bug?