Re: Index not used with IS NULL

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Dennis Gearon <gearond(at)cvc(dot)net>
Cc: Martijn van Oosterhout <kleptog(at)svana(dot)org>, Dmitry Tkach <dmitry(at)openratings(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: Index not used with IS NULL
Date: 2003-02-18 04:44:59
Message-ID: 20830.1045543499@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Dennis Gearon <gearond(at)cvc(dot)net> writes:
> so NULLs **DON'T** count in a unique index? You can have more than one
> NULL in a single column UNIQUE constraint? I guess I am showing my
> ignorance, I thought you could only have one.

Unique indexes enforce SQL92 unique constraints, which are defined by
the spec thusly (sec 4.10):

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.

If this doesn't seem right to you, you have not grokked the concept of
NULL. Two nulls are never "equal" per spec.

regards, tom lane

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2003-02-18 04:54:59 Re: Index not used with IS NULL
Previous Message Tom Lane 2003-02-18 04:40:21 Re: Index not used with IS NULL