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

On May 18, 2005, at 3:52 PM, Chris Browne wrote:

> alainm(at)pobox(dot)com (Alain) writes:
>> 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 any other to work around that?
>
> The other way to do this would involve creating a cursor against the
> table, and using suitable FETCHes to grab the portions that you
> needed.
>
> In practice, this has seemed to be the relevant answer to what the
> application developer actually wanted.
>
> The common "use case" where I see it is in a web application where
> they discover that there are 800K records, and the user only wants a
> screenful at a time.
>
> Establishing a cursor, and having the web app jump around on it, seems
> to be the right answer. (Whether it's reasonably implementable by the
> developers may be another question, but that's allowed to be a
> separate question ;-).)

In a web app, I doubt that cursors can be useful because of the
stateless nature of web interaction. I'd love to hear otherwise,
but....

Sean