pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Reinoud van Leeuwen <reinoud.v@n.leeuwen.net>
To: pgsql-sql@postgresql.org
Subject: Re: UNIQUE columnt depdening on other column???
Date: Wed, 2 Jun 2004 14:43:45 +0200
Message-ID: <20040602124345.GQ23232@spoetnik.xs4all.nl> (raw)
In-Reply-To: <20040602113937.D45E11765F@ar-sd.net>
References: <20040602113937.D45E11765F@ar-sd.net>

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
__________________________________________________

view thread (3+ messages)

Message-ID: <20040602124345.GQ23232@spoetnik.xs4all.nl>
Permalink:  ../20040602124345.GQ23232@spoetnik.xs4all.nl/
Also on:    postgresql.org/message-id/20040602124345.GQ23232@spoetnik.xs4all.nl

copy link · copy postgr.es

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: reinoud.v@n.leeuwen.net
  Subject: Re: UNIQUE columnt depdening on other column???
  In-Reply-To: <20040602124345.GQ23232@spoetnik.xs4all.nl>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox