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(dot)Jiang(at)thomson(dot)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