Sunday, September 28, 2008

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

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?
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 Gore
Database Manager
Sheep CRC
University of New England
Armidale NSW 2350

Ph: 02 6773 3789
Fax: 02 6773 3266

Sent via pgsql-general mailing list (
To make changes to your subscription:

No comments: