Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 3AFDA633E0F for ; Mon, 29 Jun 2009 04:55:56 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 11614-05 for ; Mon, 29 Jun 2009 04:55:51 -0300 (ADT) X-Greylist: delayed 00:31:59.764693 by SQLgrey-1.7.6 Received: from ews43.everyware.ch (ews43.everyware.ch [212.71.111.43]) by mail.postgresql.org (Postfix) with ESMTP id 73D3D6339AC for ; Mon, 29 Jun 2009 04:55:54 -0300 (ADT) Received: from [192.168.0.33] [212.71.105.68] by ews43.everyware.ch with ESMTP (SMTPD32-8.15) id AC07321D0064; Mon, 29 Jun 2009 09:23:51 +0200 Subject: Re: uniqueness constraint with NULLs From: Leo Mannhart To: pgsql-sql@postgresql.org In-Reply-To: <4A486872.5060906@cs.anu.edu.au> References: <4A485431.3080707@cs.anu.edu.au> <20090629062602.GB24737@a-kretschmer.de> <4A486872.5060906@cs.anu.edu.au> Content-Type: text/plain Organization: beecom AG Date: Mon, 29 Jun 2009 09:23:51 +0200 Message-Id: <1246260231.7296.2.camel@mars.lemaco.ch> Mime-Version: 1.0 X-Mailer: Evolution 2.24.3 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200906/116 X-Sequence-Number: 32721 On Mon, 2009-06-29 at 17:08 +1000, Robert Edwards wrote: > A. Kretschmer wrote: > > In response to Robert Edwards : > >> Can anyone suggest a way that I can impose uniqueness on a and b when > >> c is NULL? > > > > Sure, use a functional index: > > > > test=# create table bobtest (a int, b int, c int); > > CREATE TABLE > > test=*# create unique index idx_bobtest on > > bobtest(a,b,coalesce(c::text,'NULL')); > > CREATE INDEX > > test=*# insert into bobtest (a, b) values (1, 4); > > INSERT 0 1 > > test=*# insert into bobtest (a, b, c) values (1, 4, NULL); > > ERROR: duplicate key value violates unique constraint "idx_bobtest" > > test=!# > > Why changing the datatype of coulumn "c"? Will this not probably disable the use of the index? Why not only: lem=# create table bobtest (a int, b int, c int); CREATE TABLE lem=# create unique index idc_bobtest on bobtest (a, b, coalesce(c, 1)); CREATE INDEX lem=# insert into bobtest (a, b) values (1, 4); INSERT 0 1 lem=# insert into bobtest (a, b) values (1, 4); ERROR: duplicate key value violates unique constraint "idc_bobtest" lem=# insert into bobtest (a, b, c) values (1, 4, null); ERROR: duplicate key value violates unique constraint "idc_bobtest" lem=# > > > > Regards, Andreas > > Beautiful! > > Many thanks, > > Bob Edwards. >