From: Kevin Houle <kevin@houle.org>
To: Ries van Twisk <ries@jongert.nl>
Subject: Re: Dublicates pairs in a table.
Date: Tue, 24 Sep 2002 21:10:03 -0400
Message-ID: <3D910CEB.30704@houle.org> (raw)
References: <000201c25d90$87230c10$6500000a@IT000>
Ries van Twisk wrote:
> I have a small question which I could not clearly find in the postgreSQL
> manual.
>
> if I create this table and index
> CRAEATE TABLE test (
> id SERIAL,
> c1 VARCHAR(32),
> c2 VARCHAR(32),
> c3 VARCHAR(32)
> );
>
> CREATE UNIQUE INDEX test_idx ON test(id, c1,c2);
>
> what I try to archive here is that I don't want duplicate pais in my table:
> example
>
> INSET INTO test (c1,c2) VALUES('a', 'a'); -- Allowed
> INSET INTO test (c1,c2) VALUES('b', 'b'); -- Allowed
> INSET INTO test (c1,c2) VALUES('b', 'c'); -- Allowed
> INSET INTO test (c1,c2) VALUES('a', 'a'); -- Not allowed since we already
> have a duplicate ('a', 'a') pair
> INSET INTO test (c1,c2) VALUES('b', 'c'); -- Not allowed since we already
> have a duplicate ('b', 'c') pair
>
> etc. etc. I think you get the idea...
I have the same issue with a table that currently holds well
over 600,000 rows. The case you left out is this:
INSERT INTO test (c1,c2) VALUES('a','c');
INSERT INTO test (c1,c2) VALUES('c','a');
I want that to fail, but I haven't been able to get it to fail
using unique indexes. I presume ordering is significant. Instead,
I am doing a SELECT prior to insert to insure the pair doesn't
already exist. If you've been able to get order-independent
pairs restricted to being unique using indexes, I'd like to know
about it. :-)
Kevin
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: kevin@houle.org, ries@jongert.nl
Subject: Re: Dublicates pairs in a table.
In-Reply-To: <3D910CEB.30704@houle.org>
* 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