Re: Unique indexes not unique?

> Jimmy Mäkelä wrote:
>
>> I found that Postgres isn't behaving like I thought when using a
>> unique index in
>> combination with NULL-values...
>> Is this a bug or specified in the SQL-standard? If its a bug, is it
>> fixed in a
>> recent version? We are using 7.2.3

>> intranet=# insert into "foo" (a, b) values ('apa', null);
>> INSERT 26229706 1
>> intranet=# insert into "foo" (a, b) values ('apa', null);
>> INSERT 26229707 1
>
> I'm not sure unique index works properly for null values. I can't
> explain, why. Maybe it comes from SQL standard - null i a special value
> and can't be compared using default operators to other non null values:
> 1>null =null
> 1<null =null
> 1=null =null

Null is not a value or even a "special" value, it is supposed to represent
the absence of a value. It means either "not applicable" or "not known".

It doesn't make sense to say whether one null is the same as another, a
null is an absence, a hole. As a result, you can't really talk about
comparing two nulls, only testing whether a value is null.

If you are using a null in a situation where it should be unique, you
probably want a value instead. Can't say more without an actual example.

- Richard Huxton