Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id B12FD633A35 for ; Mon, 29 Jun 2009 04:08:51 -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 02649-09 for ; Mon, 29 Jun 2009 04:08:47 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from anumail8.anu.edu.au (anumail8.anu.edu.au [130.56.64.134]) by mail.postgresql.org (Postfix) with ESMTP id 2E11E63395B for ; Mon, 29 Jun 2009 04:08:44 -0300 (ADT) Received: from mailout1.anu.edu.au (mailout1.anu.edu.au [130.56.64.232]) by anumail8.anu.edu.au (8.14.3/8.14.3) with ESMTP id n5T78evq029325; Mon, 29 Jun 2009 17:08:40 +1000 (EST) Received: from mailout1.anu.edu.au (localhost.localdomain [127.0.0.1]) by localhost (Postfix) with SMTP id 45D3C14A09B5; Mon, 29 Jun 2009 17:08:40 +1000 (EST) Received: from smtphost.anu.edu.au (ds2.anu.edu.au [130.56.64.54]) by mailout1.anu.edu.au (Postfix) with ESMTP id B5FA214A09AD; Mon, 29 Jun 2009 17:08:39 +1000 (EST) Received: from dcsmail.anu.edu.au (dcsmail.anu.edu.au [150.203.164.16]) by smtphost.anu.edu.au (8.14.3/8.14.3) with ESMTP id n5T78dZf018669; Mon, 29 Jun 2009 17:08:39 +1000 (EST) Received: from littleblue.anu.edu.au ([150.203.160.52]) by dcsmail.anu.edu.au with esmtp (Exim 4.63) (envelope-from ) id 1MLAyg-0004YM-Ti; Mon, 29 Jun 2009 17:08:39 +1000 Message-ID: <4A486872.5060906@cs.anu.edu.au> Date: Mon, 29 Jun 2009 17:08:34 +1000 From: Robert Edwards User-Agent: Thunderbird 2.0.0.18 (X11/20081125) MIME-Version: 1.0 To: "A. Kretschmer" CC: pgsql-sql@postgresql.org References: <4A485431.3080707@cs.anu.edu.au> <20090629062602.GB24737@a-kretschmer.de> In-Reply-To: <20090629062602.GB24737@a-kretschmer.de> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-SA-Exim-Connect-IP: 150.203.160.52 X-SA-Exim-Mail-From: bob@cs.anu.edu.au Subject: Re: uniqueness constraint with NULLs X-SA-Exim-Version: 4.2.1 (built Tue, 09 Jan 2007 17:23:22 +0000) X-SA-Exim-Scanned: Yes (on dcsmail.anu.edu.au) X-PMX-Version: 5.5.6.374947, Antispam-Engine: 2.7.2.376379, Antispam-Data: 2009.6.29.65715 internal X-PMX-Outbound: anu.edu.au X-PMX-Info: from-anu X-PerlMx-Spam: Gauge=IIIIIIII, Probability=8%, Report=' BODY_SIZE_1000_LESS 0, BODY_SIZE_2000_LESS 0, BODY_SIZE_5000_LESS 0, BODY_SIZE_600_699 0, BODY_SIZE_7000_LESS 0, __BOUNCE_CHALLENGE_SUBJ 0, __CT 0, __CTE 0, __CT_TEXT_PLAIN 0, __HAS_MSGID 0, __MIME_TEXT_ONLY 0, __MIME_VERSION 0, __MOZILLA_MSGID 0, __SANE_MSGID 0, __TO_MALFORMED_2 0, __USER_AGENT 0' X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200906/114 X-Sequence-Number: 32719 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=!# > > > Regards, Andreas Beautiful! Many thanks, Bob Edwards.