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 DB630D1D2A0 for ; Tue, 6 Apr 2004 12:15:35 -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 86581-05 for ; Tue, 6 Apr 2004 12:15:32 -0300 (ADT) Received: from matrix.gatewaynet.com (matrix.gatewaynet.com [217.19.69.50]) by svr1.postgresql.org (Postfix) with ESMTP id 15A86D1CCDC for ; Tue, 6 Apr 2004 12:15:31 -0300 (ADT) Received: from matrix.gatewaynet.com (localhost.localdomain [127.0.0.1]) by matrix.gatewaynet.com (8.12.8/8.12.8) with ESMTP id i36FI3Nu004694; Tue, 6 Apr 2004 18:18:03 +0300 Received: from localhost (achill@localhost) by matrix.gatewaynet.com (8.12.8/8.12.8/Submit) with ESMTP id i36FI3RW004690; Tue, 6 Apr 2004 18:18:03 +0300 Date: Tue, 6 Apr 2004 18:18:03 +0300 (EEST) From: Achilleus Mantzios To: Robert Treat Cc: pgsql-sql@postgresql.org Subject: Re: partial unique constraint In-Reply-To: <1081261772.31785.48.camel@camel> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/38 X-Sequence-Number: 17413 O kyrios Robert Treat egrapse stis Apr 6, 2004 : > Trying to come up with the proper syntax to meet the following criteria: > > create table foo (bar integer, baz boolean UNIQUE (bar, baz = true)); You could simply leave your table as is. A null vale on baz (equivalent of 'false') will have no impact on the constraint. A 't' value (equivalent to 'true') will enforce the constraint. In other words you can convert your problem as baz == null <==> baz='false' baz == 't' <==> baz='true' (you must never use baz='f') > > 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 > > Robert Treat > -- -Achilleus