X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 88E8DD1EA53 for ; Tue, 6 Apr 2004 12:26:13 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 94216-02 for ; Tue, 6 Apr 2004 12:26:11 -0300 (ADT) Received: from anchor-post-31.mail.demon.net (anchor-post-31.mail.demon.net [194.217.242.89]) by svr1.postgresql.org (Postfix) with ESMTP id E3070D1E138 for ; Tue, 6 Apr 2004 12:26:10 -0300 (ADT) Received: from tmsl-adsl.demon.co.uk ([80.177.114.181] helo=bacon.tmsl.demon.co.uk) by anchor-post-31.mail.demon.net with esmtp (Exim 3.35 #1) id 1BAsSn-000FwT-0V; Tue, 06 Apr 2004 16:26:09 +0100 Date: Tue, 6 Apr 2004 16:26:02 +0100 From: Paul Thomas To: Robert Treat Cc: "pgsql-sql @ postgresql . org" Subject: Re: partial unique constraint Message-ID: <20040406162602.F6112@bacon> References: <1081261772.31785.48.camel@camel> Mime-Version: 1.0 Content-Type: text/plain; format=flowed; charset=ISO-8859-1 In-Reply-To: <1081261772.31785.48.camel@camel>; from xzilla@users.sourceforge.net on Tue, Apr 06, 2004 at 15:29:32 +0100 X-Mailer: Balsa 1.2.3 Lines: 30 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/41 X-Sequence-Number: 17416 On 06/04/2004 15:29 Robert Treat wrote: > Trying to come up with the proper syntax to meet the following criteria: > > create table foo (bar integer, baz boolean UNIQUE (bar, baz = true)); > > note the above syntax is not correct, but should demonstrate what i'm > trying to do; I want to add a unique constraint such that we only allow > one case of bar and baz = true... i can have unlimited bar and baz = > false, and there can be multiple bar and baz = true if the bars are > different... did some doc reading and mail list searching but a valid > syntax for this seems to be escaping me... > > btw I'm pretty sure I could do this with an external trigger, but am > wondering about a constraint oriented approach What about create table foo (bar integer, baz boolean); create unique index foo_bar_baz on foo(bar, baz) where baz = true; -- Paul Thomas +------------------------------+---------------------------------------------+ | Thomas Micro Systems Limited | Software Solutions for Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +------------------------------+---------------------------------------------+