Content-Type: text/plain;
  charset="iso-8859-1"
From: Richard Huxton <dev@archonet.com>
Organization: Archonet Ltd
To: Kevin Houle <kevin@houle.org>, pgsql-sql@postgresql.org
Subject: Re: Dublicates pairs in a table.
Date: Fri, 27 Sep 2002 13:54:54 +0100
User-Agent: KMail/1.4.3
References: <000201c25d90$87230c10$6500000a@IT000> <3D910CEB.30704@houle.org>
In-Reply-To: <3D910CEB.30704@houle.org>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200209271354.54187.dev@archonet.com>

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 sor=
ted=20
order.

richardh=3D> CREATE TABLE foo (a text, b text);
CREATE
richardh=3D> CREATE UNIQUE INDEX foo_both_uniq ON foo ( ord_fn(a,b) );
ERROR:  DefineIndex: index function must be marked iscachable
richardh=3D> \i ordfn.txt
DROP
CREATE
richardh=3D> CREATE UNIQUE INDEX foo_both_uniq ON foo ( ord_fn(a,b) );
CREATE
richardh=3D> insert into foo values ('aa','bb');
INSERT 332596 1
richardh=3D> insert into foo values ('aa','cc');
INSERT 332597 1
richardh=3D> insert into foo values ('bb','aa');
ERROR:  Cannot insert a duplicate key into unique index foo_both_uniq
richardh=3D> 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);

--=20
  Richard Huxton