X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id CA20711F6085 for ; Wed, 19 Apr 2006 03:01:10 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: 0 X-Spam-Level: X-Spam-Status: No, score=0 required=5 tests=[none] Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id Ru3R+owde6rC for ; Wed, 19 Apr 2006 03:01:05 -0300 (ADT) X-Greylist: delayed 07:52:39.434127 by SQLgrey- Received: from mx1.vodamail.co.za (mx1.vodamail.co.za [196.11.146.148]) by postgresql.org (Postfix) with ESMTP id 8A97A11F6700 for ; Wed, 19 Apr 2006 03:01:06 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by mx1.vodamail.co.za (Postfix) with ESMTP id 3CF1660B2E for ; Wed, 19 Apr 2006 08:01:03 +0200 (SAST) Received: from mx1.vodamail.co.za ([127.0.0.1]) by localhost (mx1.vodamail.co.za [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 00962-07 for ; Wed, 19 Apr 2006 08:01:03 +0200 (SAST) Received: from josh.flame2.flame.co.za (unknown [10.48.20.8]) by mx1.vodamail.co.za (Postfix) with ESMTP id 6179667E23 for ; Wed, 19 Apr 2006 08:00:59 +0200 (SAST) Received: from [127.0.0.1] (localhost.localdomain [127.0.0.1]) by josh.flame2.flame.co.za (8.12.11/8.12.11) with ESMTP id k3J60dRi003936 for ; Wed, 19 Apr 2006 08:00:42 +0200 Subject: Re: Multicolumn order by From: Theo Kramer Reply-To: theo@flame.co.za To: pgsql-performance@postgresql.org In-Reply-To: <19819.1145401713@sss.pgh.pa.us> References: <1145398075.3048.18.camel@josh> <19819.1145401713@sss.pgh.pa.us> Content-Type: text/plain Organization: Flame Computing Enterprises cc Message-Id: <1145426438.3048.25.camel@josh> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Wed, 19 Apr 2006 08:00:39 +0200 Content-Transfer-Encoding: 7bit X-Virus-Scanned: amavisd-new at vodamail.co.za X-Archive-Number: 200604/421 X-Sequence-Number: 18505 On Wed, 2006-04-19 at 01:08, Tom Lane wrote: > Theo Kramer writes: > > select * from mytable where > > (c1 = 'c1v' and c2 = 'c2v' and c3 >= 'c3v') or > > (c1 = 'c1v' and c2 > 'c2v') or > > (c1 > 'c1v') > > order by c1, c2, c3; > > Yeah ... what you really want is the SQL-spec row comparison operator > > select ... where (c1,c2,c3) >= ('c1v','c2v','c3v') order by c1,c2,c3; > > This does not work properly in any current PG release :-( but it does > work and is optimized well in CVS HEAD. See eg this thread > http://archives.postgresql.org/pgsql-hackers/2006-02/msg00209.php That is awesome - been fighting with porting my isam based stuff onto sql for a long time and the row comparison operator is exactly what I have been looking for. I tried this on my test system running 8.1.3 and appears to work fine. Appreciate it if you could let me know in what cases it does not work properly. -- Regards Theo