pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUNIQUE columnt depdening on other column???
3+ messages / 3 participants
[nested] [flat]
* UNIQUE columnt depdening on other column???
@ 2004-06-02 11:39 Andrei Bintintan <klodoma@ar-sd.net>
0 siblings, 2 replies; 3+ messages in thread
From: Andrei Bintintan @ 2004-06-02 11:39 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: UNIQUE columnt depdening on other column???
@ 2004-06-02 12:21 Richard Huxton <dev@archonet.com>
parent: Andrei Bintintan <klodoma@ar-sd.net>
1 sibling, 0 replies; 3+ messages in thread
From: Richard Huxton @ 2004-06-02 12:21 UTC (permalink / raw)
To: Andrei Bintintan <klodoma@ar-sd.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: UNIQUE columnt depdening on other column???
@ 2004-06-02 12:43 Reinoud van Leeuwen <reinoud.v@n.leeuwen.net>
parent: Andrei Bintintan <klodoma@ar-sd.net>
1 sibling, 0 replies; 3+ messages in thread
From: Reinoud van Leeuwen @ 2004-06-02 12:43 UTC (permalink / raw)
To: pgsql-sql
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
__________________________________________________
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-06-02 12:43 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-06-02 11:39 UNIQUE columnt depdening on other column??? Andrei Bintintan <klodoma@ar-sd.net>
2004-06-02 12:21 ` Richard Huxton <dev@archonet.com>
2004-06-02 12:43 ` Reinoud van Leeuwen <reinoud.v@n.leeuwen.net>
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