Re: NULL values seem to short-circuit my unique index

You can always add a constraint that these columns cannot be NULL
themselves. That removes this problem.

On Sep 28, 2008, at 11:17 PM, Klint Gore <kgore4(at)une(dot)edu(dot)au> wrote:

> Matthew Wilson wrote:
>> I'm trying to comprehend how NULL values interact with unique
>> indexes.
>> It seems like I can insert two rows with NULL values in a column
>> with a
>> unique constraint just fine.
>>
>> Is there something special about NULL? Can anyone post some links to
>> explain what is going on?
>>
>
> http://www.postgresql.org/docs/8.3/interactive/ddl-constraints.html#AEN2058
> Last paragraph just above 5.3.4.
>
>> What's the deal with NULL?
>>
>
> NULL = NULL is not true, it's null
> NULL <> NULL is not false, it's null
>
> It's the normal SQL 3 state logic (true/false/null) with only the
> true value from the comparison causing the constraint violation.
> Think of the unique constraint check like "does this value equal any
> other value already recorded".
>
> klint.
>
> --
> Klint Gore
> Database Manager
> Sheep CRC
> A.G.B.U.
> University of New England
> Armidale NSW 2350
>
> Ph: 02 6773 3789 Fax: 02 6773 3266
> EMail: kgore4(at)une(dot)edu(dot)au
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general