Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 0B04C63291B for ; Mon, 29 Jun 2009 03:31:13 -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 94013-08 for ; Mon, 29 Jun 2009 03:31:05 -0300 (ADT) X-Greylist: delayed 00:05:02.755852 by SQLgrey-1.7.6 Received: from moutng.kundenserver.de (moutng.kundenserver.de [212.227.17.10]) by mail.postgresql.org (Postfix) with ESMTP id 5130F632912 for ; Mon, 29 Jun 2009 03:31:08 -0300 (ADT) Received: from webserv.wug.de ([194.25.154.138]) by mrelayeu.kundenserver.de (node=mrbap0) with ESMTP (Nemesis) id 0MKsym-1MLAJW2XMe-000Ncr; Mon, 29 Jun 2009 08:26:03 +0200 Received: from kretschmer by webserv.wug.de with local (Exim 3.36 #1) id 1MLAJW-0008DI-00 for pgsql-sql@postgresql.org; Mon, 29 Jun 2009 08:26:02 +0200 Date: Mon, 29 Jun 2009 08:26:02 +0200 From: "A. Kretschmer" To: pgsql-sql@postgresql.org Subject: Re: uniqueness constraint with NULLs Message-ID: <20090629062602.GB24737@a-kretschmer.de> References: <4A485431.3080707@cs.anu.edu.au> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <4A485431.3080707@cs.anu.edu.au> X-OS: Debian/GNU Linux - weil ich es mir Wert bin! User-Agent: Mutt/1.5.9i X-Provags-ID: V01U2FsdGVkX1/NPQazMDH+XLB/eAT2fpKGSHVNgEkF1cj77EX qKWwPN6J7pE8AqwxAtGaBX1r+vFYupgx160baWOlEDjTkAB1oT q6P1eeRfamUBrzJIgs2Rg== X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.034 tagged_above=0 required=5 tests=AWL=0.034 X-Spam-Level: X-Archive-Number: 200906/113 X-Sequence-Number: 32718 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 -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net