Subject: Re: Does Postgresql have a similar pseudo-column "ROWNUM" as
From: Ragnar =?ISO-8859-1?Q?Hafsta=F0?= <gnari@simnet.is>
To: Alain <alainm@pobox.com>
Cc: Sql-Postgre <pgsql-sql@postgresql.org>
In-Reply-To: <428A2E81.2060501@pobox.com>
References: 
 <DCD4903594CCF34CAFE200BDF3448D8D5B291A@tshuscodenmbx01.ERF.THOMSON.COM>
	<20050517170636.GC12405@phlogiston.dyndns.org>
	<428A2E81.2060501@pobox.com>
Content-Type: text/plain
Date: Tue, 17 May 2005 18:12:19 +0000
Message-Id: <1116353539.26520.5.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Tue, 2005-05-17 at 14:48 -0300, Alain wrote:
> 
> Andrew Sullivan escreveu:
> > On Thu, May 12, 2005 at 01:07:00PM -0600, Dennis.Jiang@thomson.com wrote:
> > 
> >>Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? If
> >>so, we can write the following query:
> > 
> > 
> > No.  What is the purpose of your query?  You could use ORDER BY and
> > LIMIT..OFFSET to do what you want. I think.
> 
> The problem is probably speed. I have done a lot of tests, and when 
> OFFSET gets to a few thousands on a multimega-recs database, it gets 
> very very slow... 

is there not a similar loss of speed using ROWNUM on oracle?

> ... Is there any other to work around that?

if you are ordering by a unique key, you can use the key value
in a WHERE clause.

select ... where ukey>? order by ukey limit 100 offset 100;

(the ? is placeholder for the last value of ukey returned
from previous select)

gnari