X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id AA9A4D1CA04 for ; Wed, 2 Jun 2004 09:22:13 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 07184-04 for ; Wed, 2 Jun 2004 12:22:16 +0000 (GMT) Received: from anchor-post-37.mail.demon.net (anchor-post-36.mail.demon.net [194.217.242.86]) by svr1.postgresql.org (Postfix) with ESMTP id CDEF8D1B1F7 for ; Wed, 2 Jun 2004 09:22:09 -0300 (ADT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-37.mail.demon.net with esmtp (Exim 3.35 #1) id 1BVUky-0005DM-0b; Wed, 02 Jun 2004 13:22:10 +0100 Received: from [192.168.1.17] (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id A715316247; Wed, 2 Jun 2004 13:21:53 +0100 (BST) Message-ID: <40BDC661.6070305@archonet.com> Date: Wed, 02 Jun 2004 13:21:53 +0100 From: Richard Huxton User-Agent: Mozilla Thunderbird 0.6 (X11/20040502) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Andrei Bintintan Cc: pgsql-sql@postgresql.org Subject: Re: UNIQUE columnt depdening on other column??? References: <20040602113937.D45E11765F@ar-sd.net> In-Reply-To: <20040602113937.D45E11765F@ar-sd.net> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Archive-Number: 200406/11 X-Sequence-Number: 17956 Andrei Bintintan wrote: > Hi, > > I have a problem. > > Let's say I have the following table: > CREATE TABLE rekl_element( > id serial PRIMARY KEY, > active boolean NOT NULL DEFAULT 'y', > num int4 NOT NULL, > text varchar(10) > ); > > Now I want that "num" column is "unique" but only for those columns that > have active='y'. For the columns that have active='f' I don't care if num is > unique or not. I'm asking this because num will be doubled some times. Non-standard but elegant: CREATE UNIQUE INDEX my_partially_unique_index ON rekl_element (num) WHERE active; -- Richard Huxton Archonet Ltd