Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 772D8476182
	for <pgsql-sql@postgresql.org>; Mon, 13 Jan 2003 07:32:07 -0500 (EST)
Received: from anchor-post-31.mail.demon.net (anchor-post-31.mail.demon.net
	[194.217.242.89])
	by postgresql.org (Postfix) with ESMTP id 9A1B6476033
	for <pgsql-sql@postgresql.org>; Mon, 13 Jan 2003 07:32:06 -0500 (EST)
Received: from mwynhau.demon.co.uk ([193.237.186.96]
 helo=mainbox.archonet.com)
	by anchor-post-31.mail.demon.net with esmtp (Exim 3.35 #1)
	id 18Y3l8-000CCc-0V; Mon, 13 Jan 2003 12:32:06 +0000
Received: from localhost (localhost.localdomain [127.0.0.1])
	by mainbox.archonet.com (Postfix) with ESMTP
	id C81CE15A6D; Mon, 13 Jan 2003 12:32:05 +0000 (GMT)
Received: from archonet.com (localhost.localdomain [127.0.0.1])
	by mainbox.archonet.com (Postfix) with SMTP
	id 2F8F51597A; Mon, 13 Jan 2003 12:32:05 +0000 (GMT)
Received: from 192.168.1.16 (SquirrelMail authenticated user dev)
	by mainbox.archonet.com with HTTP;
	Mon, 13 Jan 2003 12:32:05 -0000 (GMT)
Message-ID: <1477.192.168.1.16.1042461125.squirrel@mainbox.archonet.com>
In-Reply-To: <3E229858.4070509@klaster.net>
References: <D1045567F50DD311AB1B00508B3188E9026546D8@RINGHALS>
	<3E229858.4070509@klaster.net>
Date: Mon, 13 Jan 2003 12:32:05 -0000 (GMT)
Subject: Re: Unique indexes not unique?
From: dev@archonet.com
To: "Tomasz Myrta" <jasiek@klaster.net>
Cc: jimmy.makela@agent25.se,
	"'pgsql-sql@postgresql.org'" <pgsql-sql@postgresql.org>
X-Mailer: SquirrelMail (version 1.3.2)
MIME-Version: 1.0
Content-Type: text/plain;charset=iso-8859-1
X-Priority: 3
Importance: Normal
X-Virus-Scanned: by AMaViS snapshot-20020531
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200301/167
X-Sequence-Number: 11032

> 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