Re: Does Postgresql have a similar pseudo-column "ROWNUM" as

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