Received: from localhost (maia-4.hub.org [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id BC61E9FB289
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 26 Jul 2007 22:28:01 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 82156-06 for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 26 Jul 2007 22:27:59 -0300 (ADT)
X-Greylist: delayed 00:27:55.970079 by SQLgrey-1.7.5
Received: from region35.ru (region35.ru [217.23.134.9])
	by postgresql.org (Postfix) with ESMTP id CC2F79FB270
	for <pgsql-sql@postgresql.org>; Thu, 26 Jul 2007 22:27:58 -0300 (ADT)
Received: from [192.168.1.178] (219-89-201-48.adsl.xtra.co.nz [219.89.201.48])
	by region35.ru (Postfix) with ESMTP id 812E4B877
	for <pgsql-sql@postgresql.org>; Fri, 27 Jul 2007 05:00:26 +0400 (MSD)
Date: Fri, 27 Jul 2007 12:59:38 +1200
From: Dmitry Ruban <dmitry@ruban.biz>
X-Mailer: The Bat! (v3.62.14) Home
Reply-To: Dmitry Ruban <dmitry@ruban.biz>
X-Priority: 3 (Normal)
Message-ID: <248746715.20070727125938@ruban.biz>
To: pgsql-sql@postgresql.org
Subject: unique index on fields with possible null values
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=BAYES_50=0.001
X-Spam-Level: 
X-Archive-Number: 200707/157
X-Sequence-Number: 28892

Hello pgsql-sql,

I'm trying to find a solution for unique index on fields with possible
null values.

Example table:

CREATE TABLE test (
 a integer NOT NULL,
 b integer NULL
);

As long as unique index can't check if there are records with null
values i found the only one solution for this problem:

CREATE UNIQUE INDEX test_uniq1 ON test (a, COALESCE(b,0));

Are there any other ways of doing this?

-- 
Best regards,
 Dmitry                          mailto:dmitry@ruban.biz