Date: Fri, 27 Sep 2002 09:09:56 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Richard Huxton <dev@archonet.com>
Cc: Kevin Houle <kevin@houle.org>, <pgsql-sql@postgresql.org>
Subject: Re: Dublicates pairs in a table.
In-Reply-To: <200209271354.54187.dev@archonet.com>
Message-ID: <20020927090810.X40745-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII


On Fri, 27 Sep 2002, 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. :-)
>
> 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);

Note, that for a final system, you'll may want to also add a
delimiter that doesn't show up in $1 or $2 if ('a','ab') and
('aa','b') aren't supposed to cause an error.