Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id BFE964763DD for ; Fri, 27 Sep 2002 12:09:23 -0400 (EDT) Received: from beamish.nsd.ca (unknown [205.150.156.194]) by postgresql.org (Postfix) with ESMTP id 0A4D7476389 for ; Fri, 27 Sep 2002 12:09:23 -0400 (EDT) Received: (from smap@localhost) by beamish.nsd.ca (8.9.3/8.9.3) id MAA10594; Fri, 27 Sep 2002 12:09:20 -0400 X-Authentication-Warning: beamish.nsd.ca: smap set sender to using -f Received: from reddog.nsd.ca(192.168.101.30) by beamish.nsd.ca via smap (V2.1/2.1+anti-relay+anti-spam) id xma010592; Fri, 27 Sep 02 12:09:04 -0400 Received: from nsd.ca (jllachan-linux.nsd.ca [192.168.101.148]) by reddog.nsd.ca (8.8.7/8.8.7) with ESMTP id MAA26779; Fri, 27 Sep 2002 12:08:50 -0400 Message-ID: <3D94849E.4A769993@nsd.ca> Date: Fri, 27 Sep 2002 12:17:34 -0400 From: Jean-Luc Lachance X-Mailer: Mozilla 4.77 [en] (X11; U; Linux 2.4.9-31 i686) X-Accept-Language: en MIME-Version: 1.0 To: Richard Huxton Cc: Kevin Houle , pgsql-sql@postgresql.org Subject: Re: Dublicates pairs in a table. References: <000201c25d90$87230c10$6500000a@IT000> <3D910CEB.30704@houle.org> <200209271354.54187.dev@archonet.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200209/381 X-Sequence-Number: 9658 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)