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

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

From: Klint Gore <kgore4(at)une(dot)edu(dot)au>
To: Matthew Wilson <matt(at)tplus1(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: NULL values seem to short-circuit my unique index
Date: 2008-09-29 03:17:15
Message-ID: 48E048BB.6070904@une.edu.au (view raw or flat)
Thread:
Lists: pgsql-general
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


In response to

Responses

pgsql-general by date

Next:From: Richard BroersmaDate: 2008-09-29 04:43:49
Subject: Re: inserting to a multi-table view
Previous:From: Tom LaneDate: 2008-09-29 03:04:50
Subject: Re: NULL values seem to short-circuit my unique index

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