Skip site navigation (1) Skip section navigation (2)

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

From: Tom Allison <tom(at)tacocat(dot)net>
To: Klint Gore <kgore4(at)une(dot)edu(dot)au>
Cc: Matthew Wilson <matt(at)tplus1(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: NULL values seem to short-circuit my unique index
Date: 2008-09-29 09:49:36
Message-ID: C451EE24-DBFD-45F2-AAA9-A0DD027A905E@tacocat.net (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

pgsql-general by date

Next:From: Simon RiggsDate: 2008-09-29 10:12:20
Subject: Re: pg_start_backup() takes too long
Previous:From: Ivan ZolotukhinDate: 2008-09-29 09:39:58
Subject: Re: pg_start_backup() takes too long

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group