From: Jean-Luc Lachance <jllachan@nsd.ca>
To: Richard Huxton <dev@archonet.com>
Cc: Kevin Houle <kevin@houle.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: Dublicates pairs in a table.
Date: Fri, 27 Sep 2002 12:17:34 -0400
Message-ID: <3D94849E.4A769993@nsd.ca> (raw)
References: <000201c25d90$87230c10$6500000a@IT000>
<3D910CEB.30704@houle.org>
<200209271354.54187.dev@archonet.com>
What's wrong with
CREATE UNIQUE INDEX foo_both_uniq ON foo(a,b);
???
Richard Huxton wrote:
>
> On Wednesday 25 Sep 2002 2:10 am, Kevin Houle wrote:
> > 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. :-)
>
> Functional indexes sir - define a function that puts the columns into a sorted
> order.
>
> richardh=> CREATE TABLE foo (a text, b text);
> CREATE
> richardh=> CREATE UNIQUE INDEX foo_both_uniq ON foo ( ord_fn(a,b) );
> ERROR: DefineIndex: index function must be marked iscachable
> richardh=> \i ordfn.txt
> DROP
> CREATE
> richardh=> CREATE UNIQUE INDEX foo_both_uniq ON foo ( ord_fn(a,b) );
> CREATE
> richardh=> insert into foo values ('aa','bb');
> INSERT 332596 1
> richardh=> insert into foo values ('aa','cc');
> INSERT 332597 1
> richardh=> insert into foo values ('bb','aa');
> ERROR: Cannot insert a duplicate key into unique index foo_both_uniq
> richardh=> insert into foo values ('aa','bb');
> ERROR: Cannot insert a duplicate key into unique index foo_both_uniq
>
> Function defined as:
> CREATE FUNCTION ord_fn (text,text) RETURNS text AS '
> SELECT (CASE
> WHEN $1 < $2
> THEN $1 || $2
> ELSE $2 || $1
> END) as t;
> ' LANGUAGE SQL WITH (iscachable);
>
> --
> Richard Huxton
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
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: jllachan@nsd.ca, dev@archonet.com, kevin@houle.org
Subject: Re: Dublicates pairs in a table.
In-Reply-To: <3D94849E.4A769993@nsd.ca>
* 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