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

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 ;-).)
--
(format nil "~S(at)~S" "cbbrowne" "acm.org")
http://www.ntlug.org/~cbbrowne/sap.html
Rules of the Evil Overlord #78. "I will not tell my Legions of Terror
"And he must be taken alive!" The command will be: ``And try to take
him alive if it is reasonably practical.''"
<http://www.eviloverlord.com/>