From: Sean Davis <sdavis2@mail.nih.gov>
To: Chris Browne <cbbrowne@acm.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: Does Postgresql have a similar pseudo-column "ROWNUM" as
Date: Wed, 18 May 2005 16:20:26 -0400
Message-ID: <bd64363b8e45406a72b43db31af55e76@mail.nih.gov> (raw)
In-Reply-To: <60mzqsqpku.fsf@dba2.int.libertyrms.com>
References: <DCD4903594CCF34CAFE200BDF3448D8D5B291A@tshuscodenmbx01.ERF.THOMSON.COM>
<20050517170636.GC12405@phlogiston.dyndns.org>
<428A2E81.2060501@pobox.com>
<60mzqsqpku.fsf@dba2.int.libertyrms.com>
On May 18, 2005, at 3:52 PM, Chris Browne wrote:
> alainm@pobox.com (Alain) writes:
>> 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 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sdavis2@mail.nih.gov, cbbrowne@acm.org
Subject: Re: Does Postgresql have a similar pseudo-column "ROWNUM" as
In-Reply-To: <bd64363b8e45406a72b43db31af55e76@mail.nih.gov>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox