From klodoma@ar-sd.net Wed Jun  2 11:39:38 2004
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 AA2D5D1B345
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  2 Jun 2004 08:39:38 -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 85103-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  2 Jun 2004 11:39:40 +0000 (GMT)
Received: from ar-sd.net (unknown [81.196.32.112])
	by svr1.postgresql.org (Postfix) with ESMTP id F1850D1B1EC
	for <pgsql-sql@postgresql.org>; Wed,  2 Jun 2004 08:39:35 -0300 (ADT)
Received: from localhost (localhost [127.0.0.1])
	by ar-sd.net (Postfix) with ESMTP id 0324020FF8
	for <pgsql-sql@postgresql.org>; Wed,  2 Jun 2004 14:39:38 +0300 (EEST)
Received: from forge (unknown [192.168.0.11])
	by ar-sd.net (Postfix) with ESMTP id D45E11765F
	for <pgsql-sql@postgresql.org>; Wed,  2 Jun 2004 14:39:37 +0300 (EEST)
From: "Andrei Bintintan" <klodoma@ar-sd.net>
To: <pgsql-sql@postgresql.org>
Subject: UNIQUE columnt depdening on other column???
Date: Wed, 2 Jun 2004 14:39:45 +0300
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0004_01C448AF.72F14E30"
X-Mailer: Microsoft Office Outlook, Build 11.0.5510
Thread-Index: AcRIlkwT/lI/NsPuQsy7FhfdwtO6Cg==
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2739.300
Message-Id: <20040602113937.D45E11765F@ar-sd.net>
X-Virus-Scanned: by AMaViS snapshot-20020531
X-Virus-Scanned: by amavisd-new at hub.org
X-Archive-Number: 200406/10
X-Sequence-Number: 17955

This is a multi-part message in MIME format.

------=_NextPart_000_0004_01C448AF.72F14E30
Content-Type: text/plain;
	charset="US-ASCII"
Content-Transfer-Encoding: 7bit

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. 
 
How can I write a constraint, trigger function... etc to check this?

Thanks in advance. 
Andy.

------=_NextPart_000_0004_01C448AF.72F14E30
Content-Type: text/html;
	charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Dus-ascii">
<META content=3D"MSHTML 6.00.2737.800" name=3DGENERATOR></HEAD>
<BODY>
<DIV><SPAN class=3D389423411-02062004><FONT face=3DArial size=3D2>Hi,=20
</FONT></SPAN></DIV>
<DIV><SPAN class=3D389423411-02062004><FONT face=3DArial=20
size=3D2></FONT></SPAN>&nbsp;</DIV>
<DIV><SPAN class=3D389423411-02062004><FONT face=3DArial size=3D2>I have a=
=20
problem.</FONT></SPAN></DIV>
<DIV><SPAN class=3D389423411-02062004><FONT face=3DArial=20
size=3D2></FONT></SPAN>&nbsp;</DIV>
<DIV><SPAN class=3D389423411-02062004><FONT face=3DArial size=3D2>Let's say=
 I have the=20
following table:</FONT></SPAN></DIV>
<DIV><SPAN class=3D389423411-02062004><FONT color=3D#0000ff>
<P><FONT face=3DArial size=3D2>CREATE</FONT></FONT><FONT size=3D2><FONT fac=
e=3DArial>=20
<FONT color=3D#0000ff>TABLE</FONT> rekl_element<FONT color=3D#808080>(</FON=
T><BR>id=20
serial <FONT color=3D#0000ff>PRIMARY</FONT> <FONT color=3D#0000ff>KEY</FONT=
><FONT=20
color=3D#808080>,</FONT><BR><FONT color=3D#0000ff>active</FONT> <FONT=20
color=3D#0000ff>boolean</FONT> <FONT color=3D#0000ff>NOT</FONT> <FONT=20
color=3D#0000ff>NULL</FONT> <FONT color=3D#0000ff>DEFAULT</FONT> <B><FONT=
=20
color=3D#ff0000>'y'</B></FONT></FONT></FONT><FONT size=3D2><FONT=20
face=3DArial>,<BR><SPAN class=3D389423411-02062004></SPAN>n<SPAN=20
class=3D389423411-02062004>um int4 NOT NULL,<BR>text=20
varchar(10)</SPAN></FONT></FONT><FONT size=3D2><FONT face=3DArial><BR><FONT=
=20
color=3D#808080>);</P></FONT></FONT></FONT></SPAN></DIV>
<DIV><SPAN class=3D389423411-02062004><FONT face=3DArial size=3D2>Now I wan=
t that=20
"num" column is "unique" but only for those columns that have active=3D'y'.=
 For=20
the columns that have active=3D'f' I don't care if num is unique or not. I'=
m=20
asking this because num will be doubled some times. </FONT></SPAN></DIV>
<DIV><SPAN class=3D389423411-02062004><FONT face=3DArial=20
size=3D2></FONT></SPAN>&nbsp;</DIV>
<DIV><SPAN class=3D389423411-02062004><FONT face=3DArial size=3D2>How can I=
 write a=20
constraint, trigger function... etc to check this?<BR></FONT></SPAN></DIV>
<DIV><SPAN class=3D389423411-02062004><FONT face=3DArial size=3D2>Thanks in=
 advance.=20
</FONT></SPAN></DIV>
<DIV><SPAN class=3D389423411-02062004><FONT face=3DArial=20
size=3D2>Andy.</DIV></FONT></SPAN></BODY></HTML>

------=_NextPart_000_0004_01C448AF.72F14E30--





From dev@archonet.com Wed Jun  2 12:22:13 2004
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 <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	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 <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	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 <pgsql-sql@postgresql.org>; 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 <dev@archonet.com>
User-Agent: Mozilla Thunderbird 0.6 (X11/20040502)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Andrei Bintintan <klodoma@ar-sd.net>
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



From reinoud.v@n.leeuwen.net Wed Jun  2 12:43:46 2004
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 6A7FDD1B238
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  2 Jun 2004 09:43:46 -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 13380-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  2 Jun 2004 12:43:48 +0000 (GMT)
Received: from spoetnik.xs4all.nl (spoetnik.xs4all.nl [213.84.24.58])
	by svr1.postgresql.org (Postfix) with ESMTP id 8C377D1B257
	for <pgsql-sql@postgresql.org>; Wed,  2 Jun 2004 09:43:42 -0300 (ADT)
Received: by spoetnik.xs4all.nl (Postfix, from userid 1000)
	id 26A911CC6A; Wed,  2 Jun 2004 14:43:45 +0200 (CEST)
Date: Wed, 2 Jun 2004 14:43:45 +0200
From: Reinoud van Leeuwen <reinoud.v@n.leeuwen.net>
To: pgsql-sql@postgresql.org
Subject: Re: UNIQUE columnt depdening on other column???
Message-ID: <20040602124345.GQ23232@spoetnik.xs4all.nl>
Mail-Followup-To: pgsql-sql@postgresql.org
References: <20040602113937.D45E11765F@ar-sd.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20040602113937.D45E11765F@ar-sd.net>
User-Agent: Mutt/1.4.2.1i
X-message-flag: Warning! The sender of this mail thinks you should use a more
	secure email client.
X-Virus-Scanned: by amavisd-new at hub.org
X-Archive-Number: 200406/12
X-Sequence-Number: 17957

On Wed, Jun 02, 2004 at 02:39:45PM +0300, 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. 

You can use a little trick for this. 
Add a column 'uniqueness' that has a default nextval ('mysequence'::text).
Make an unique index on the columns (num, uniqueness).
Obviously, this will work because the column uniqueness has unique values.

Now write a trigger that sets the uniqueness column to 0 when the active 
column equals 'y'. This will result in:

- unique num columns (or the index will fail) where active = 'y'
- arbitrary num colums (index will always be unique) where active = 'n'


 
-- 
__________________________________________________
"Nothing is as subjective as reality"
Reinoud van Leeuwen    reinoud.v@n.leeuwen.net
http://www.xs4all.nl/~reinoud
__________________________________________________