pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDoes Postgresql have a similar pseudo-column "ROWNUM" as Oracle?
17+ messages / 10 participants
[nested] [flat]
* Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle?
@ 2005-05-12 19:07 Dennis.Jiang@thomson.com
2005-05-17 17:01 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Scott Marlowe <smarlowe@g2switchworks.com>
2005-05-17 17:06 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? Bruno Wolff III <bruno@wolff.to>
2005-05-17 17:06 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 3 replies; 17+ messages in thread
From: Dennis.Jiang@thomson.com @ 2005-05-12 19:07 UTC (permalink / raw)
To: pgsql-sql; +Cc: Dennis.Jiang@thomson.com
Hi:
Oracle has a pseudo-column "ROWNUM" to return the sequence number in which a row was returned when selected from a table. The first row ROWNUM is 1, the second is 2, and so on.
Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? If so, we can write the following query:
select *
from (select RowNum, pg_catalog.pg_proc.*
from pg_catalog.pg_proc) inline_view
where RowNum between 100 and 200;
Thanks,
Dennis
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Does Postgresql have a similar pseudo-column "ROWNUM" as
2005-05-12 19:07 Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? Dennis.Jiang@thomson.com
@ 2005-05-17 17:01 ` Scott Marlowe <smarlowe@g2switchworks.com>
2005-05-20 18:27 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Keith Worthington <keithw@narrowpathinc.com>
2 siblings, 1 reply; 17+ messages in thread
From: Scott Marlowe @ 2005-05-17 17:01 UTC (permalink / raw)
To: Dennis.Jiang@thomson.com; +Cc: pgsql-sql
On Thu, 2005-05-12 at 14:07, Dennis.Jiang@thomson.com wrote:
> Hi:
>
> Oracle has a pseudo-column "ROWNUM" to return the sequence number in which a row was returned when selected from a table. The first row ROWNUM is 1, the second is 2, and so on.
>
> Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? If so, we can write the following query:
>
> select *
> from (select RowNum, pg_catalog.pg_proc.*
> from pg_catalog.pg_proc) inline_view
> where RowNum between 100 and 200;
You can get a functional equivalent with a temporary sequence:
create temp sequence rownum;
select *, nextval('rownum') as rownum from sometable;
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Does Postgresql have a similar pseudo-column "ROWNUM" as
2005-05-12 19:07 Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? Dennis.Jiang@thomson.com
2005-05-17 17:01 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Scott Marlowe <smarlowe@g2switchworks.com>
@ 2005-05-20 18:27 ` Keith Worthington <keithw@narrowpathinc.com>
2005-05-20 18:42 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 1 reply; 17+ messages in thread
From: Keith Worthington @ 2005-05-20 18:27 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: pgsql-sql
On Tue, 17 May 2005 12:01:03 -0500, Scott Marlowe wrote
> On Thu, 2005-05-12 at 14:07, Dennis.Jiang@thomson.com wrote:
> > Hi:
> >
> > Oracle has a pseudo-column "ROWNUM" to return the sequence
> > number in which a row was returned when selected from a table.
> > The first row ROWNUM is 1, the second is 2, and so on.
> >
> > Does Postgresql have a similar pseudo-column "ROWNUM" as
> > Oracle? If so, we can write the following query:
> >
> > select *
> > from (select RowNum, pg_catalog.pg_proc.*
> > from pg_catalog.pg_proc) inline_view
> > where RowNum between 100 and 200;
>
> You can get a functional equivalent with a temporary sequence:
>
> create temp sequence rownum;
> select *, nextval('rownum') as rownum from sometable;
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
Scott,
I realize that this thread went off in another direction however your
suggestion proved very helpful for a problem that I was trying to solve. I
wanted the row number of a set returned by a function. Here is a chopped
version of the function that I wrote.
CREATE OR REPLACE FUNCTION func_bom(integer, integer)
RETURNS SETOF func_bom AS
$BODY$
DECLARE
v_number ALIAS FOR $1;
v_line ALIAS FOR $2;
v_type varchar(8);
r_row interface.func_so_line_bom%rowtype;
BEGIN
SELECT tbl_item.item_type INTO v_type
FROM tbl_line_item
JOIN tbl_item
ON tbl_line_item.item_id = tbl_item.id
WHERE tbl_line_item.number = v_number
AND tbl_line_item.line = v_line;
IF v_type = 'ASY' THEN
CREATE TEMP SEQUENCE row_number
INCREMENT BY 1
START WITH 1;
FOR r_row IN SELECT tbl_line_item.number,
tbl_line_item.line,
nextval('row_number') AS subline,
tbl_assembly.quantity AS bom_quantity,
tbl_assembly.component_id AS bom_item_id,
tbl_item.item_type AS bom_item_type,
tbl_item.description AS bom_item_description
FROM tbl_line_item
LEFT JOIN tbl_assembly
ON ( tbl_line_item.item_id::text =
tbl_assembly.id::text
)
JOIN tbl_item
ON ( tbl_assembly.component_id::text =
tbl_item.id::text
)
WHERE tbl_line_item.number = v_number
AND tbl_line_item.line = v_line
ORDER BY tbl_line_item.number,
tbl_line_item.line,
tbl_assembly.component_id
LOOP
RETURN NEXT r_row;
END LOOP;
DROP SEQUENCE row_number;
ELSIF v_item_type = 'THIS'
OR v_item_type = 'THAT'
OR v_item_type = 'OTHER' THEN
FOR r_row IN SELECT
[snip]
LOOP
RETURN NEXT r_row;
END LOOP;
END IF;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE STRICT;
Although I have no need to limit the output I tried it just for giggles and it
worked fine.
SELECT * FROM func_bom(12345, 1) WHERE subline between 4 AND 6;
Thanks!
Kind Regards,
Keith
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Does Postgresql have a similar pseudo-column "ROWNUM" as
2005-05-12 19:07 Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? Dennis.Jiang@thomson.com
2005-05-17 17:01 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Scott Marlowe <smarlowe@g2switchworks.com>
2005-05-20 18:27 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Keith Worthington <keithw@narrowpathinc.com>
@ 2005-05-20 18:42 ` Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 0 replies; 17+ messages in thread
From: Scott Marlowe @ 2005-05-20 18:42 UTC (permalink / raw)
To: KeithW@narrowpathinc.com; +Cc: pgsql-sql
On Fri, 2005-05-20 at 13:27, Keith Worthington wrote:
> Scott,
>
> I realize that this thread went off in another direction however your
> suggestion proved very helpful for a problem that I was trying to solve. I
> wanted the row number of a set returned by a function. Here is a chopped
> version of the function that I wrote.
>
> CREATE OR REPLACE FUNCTION func_bom(integer, integer)
> RETURNS SETOF func_bom AS
SNIP
> Although I have no need to limit the output I tried it just for giggles and it
> worked fine.
>
> SELECT * FROM func_bom(12345, 1) WHERE subline between 4 AND 6;
You're welcome. I've saved that off to my ~/pgsql directory for future
use. So, the thanks are back to you. :)
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle?
2005-05-12 19:07 Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? Dennis.Jiang@thomson.com
@ 2005-05-17 17:06 ` Bruno Wolff III <bruno@wolff.to>
2 siblings, 0 replies; 17+ messages in thread
From: Bruno Wolff III @ 2005-05-17 17:06 UTC (permalink / raw)
To: Dennis.Jiang@thomson.com; +Cc: pgsql-sql
On Thu, May 12, 2005 at 13:07:00 -0600,
Dennis.Jiang@thomson.com wrote:
> Hi:
>
> Oracle has a pseudo-column "ROWNUM" to return the sequence number in which a row was returned when selected from a table. The first row ROWNUM is 1, the second is 2, and so on.
>
> Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? If so, we can write the following query:
No.
>
> select *
> from (select RowNum, pg_catalog.pg_proc.*
> from pg_catalog.pg_proc) inline_view
> where RowNum between 100 and 200;
You could use LIMIT and OFFSET to get the values from the table for the
100th through 200th rows (though typically you want to add an ORDER BY
clause). You could have you application supply the rownum column values.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle?
2005-05-12 19:07 Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? Dennis.Jiang@thomson.com
@ 2005-05-17 17:06 ` Andrew Sullivan <ajs@crankycanuck.ca>
2005-05-17 17:48 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Alain <alainm@pobox.com>
2005-05-17 22:13 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as PFC <lists@boutiquenumerique.com>
2 siblings, 2 replies; 17+ messages in thread
From: Andrew Sullivan @ 2005-05-17 17:06 UTC (permalink / raw)
To: pgsql-sql
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.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
The whole tendency of modern prose is away from concreteness.
--George Orwell
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Does Postgresql have a similar pseudo-column "ROWNUM" as
2005-05-12 19:07 Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? Dennis.Jiang@thomson.com
2005-05-17 17:06 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? Andrew Sullivan <ajs@crankycanuck.ca>
@ 2005-05-17 17:48 ` Alain <alainm@pobox.com>
2005-05-17 18:12 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Ragnar Hafstað <gnari@simnet.is>
2005-05-18 19:52 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Chris Browne <cbbrowne@acm.org>
1 sibling, 2 replies; 17+ messages in thread
From: Alain @ 2005-05-17 17:48 UTC (permalink / raw)
To: pgsql-sql
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?
Alain
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Does Postgresql have a similar pseudo-column "ROWNUM" as
2005-05-12 19:07 Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? Dennis.Jiang@thomson.com
2005-05-17 17:06 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? Andrew Sullivan <ajs@crankycanuck.ca>
2005-05-17 17:48 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Alain <alainm@pobox.com>
@ 2005-05-17 18:12 ` Ragnar Hafstað <gnari@simnet.is>
2005-05-17 18:43 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Alain <alainm@pobox.com>
1 sibling, 1 reply; 17+ messages in thread
From: Ragnar Hafstað @ 2005-05-17 18:12 UTC (permalink / raw)
To: Alain <alainm@pobox.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Does Postgresql have a similar pseudo-column "ROWNUM" as
2005-05-12 19:07 Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? Dennis.Jiang@thomson.com
2005-05-17 17:06 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? Andrew Sullivan <ajs@crankycanuck.ca>
2005-05-17 17:48 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Alain <alainm@pobox.com>
2005-05-17 18:12 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Ragnar Hafstað <gnari@simnet.is>
@ 2005-05-17 18:43 ` Alain <alainm@pobox.com>
2005-05-17 20:11 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Andrew Sullivan <ajs@crankycanuck.ca>
2005-05-17 22:03 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Ragnar Hafstað <gnari@simnet.is>
0 siblings, 2 replies; 17+ messages in thread
From: Alain @ 2005-05-17 18:43 UTC (permalink / raw)
To: pgsql-sql
>>>
>>>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)
I tried that. It does not work in the generic case: 6 MegaRec, telephone
listing, alphabetical order. The problem is that somewhere there is a
single user with too many entries (over 1000). I even tried to filter
the repetitions, but somewhere I get stuck if one guy has too mny
entries (one for each phone number).
I tried using both the name and the primary key (with a combined index),
to get faster to the record I want, but I was not sucessfull in building
a where clause.
I would appreciate any help, in fact this is my primary reason for
joining this list ;-)
Alain
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Does Postgresql have a similar pseudo-column "ROWNUM" as
2005-05-12 19:07 Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? Dennis.Jiang@thomson.com
2005-05-17 17:06 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? Andrew Sullivan <ajs@crankycanuck.ca>
2005-05-17 17:48 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Alain <alainm@pobox.com>
2005-05-17 18:12 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Ragnar Hafstað <gnari@simnet.is>
2005-05-17 18:43 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Alain <alainm@pobox.com>
@ 2005-05-17 20:11 ` Andrew Sullivan <ajs@crankycanuck.ca>
1 sibling, 0 replies; 17+ messages in thread
From: Andrew Sullivan @ 2005-05-17 20:11 UTC (permalink / raw)
To: Alain <alainm@pobox.com>; +Cc: pgsql-sql
On Tue, May 17, 2005 at 03:43:32PM -0300, Alain wrote:
>
> I tried using both the name and the primary key (with a combined index),
> to get faster to the record I want, but I was not sucessfull in building
> a where clause.
>
> I would appreciate any help, in fact this is my primary reason for
> joining this list ;-)
Well, then, table schemas, data distribution, EXPLAIN and EXPLAIN
ANALYSE output, and some statement of what you're trying to get out
is likely what we need to see.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
This work was visionary and imaginative, and goes to show that visionary
and imaginative work need not end up well.
--Dennis Ritchie
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Does Postgresql have a similar pseudo-column "ROWNUM" as
2005-05-12 19:07 Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? Dennis.Jiang@thomson.com
2005-05-17 17:06 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? Andrew Sullivan <ajs@crankycanuck.ca>
2005-05-17 17:48 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Alain <alainm@pobox.com>
2005-05-17 18:12 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Ragnar Hafstað <gnari@simnet.is>
2005-05-17 18:43 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Alain <alainm@pobox.com>
@ 2005-05-17 22:03 ` Ragnar Hafstað <gnari@simnet.is>
2005-05-18 02:16 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Alain <alainm@pobox.com>
1 sibling, 1 reply; 17+ messages in thread
From: Ragnar Hafstað @ 2005-05-17 22:03 UTC (permalink / raw)
To: Alain <alainm@pobox.com>; +Cc: pgsql-sql
On Tue, 2005-05-17 at 15:43 -0300, Alain wrote:
> [how to solve the get next 100 records problem]
I am assuming this is for a web like interface, in other words that
cursors are not applicable
> > [me]
> > 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)
>
> I tried that. It does not work in the generic case: 6 MegaRec, telephone
> listing, alphabetical order. The problem is that somewhere there is a
> single user with too many entries (over 1000). I even tried to filter
> the repetitions, but somewhere I get stuck if one guy has too mny
> entries (one for each phone number).
>
> I tried using both the name and the primary key (with a combined index),
> to get faster to the record I want, but I was not sucessfull in building
> a where clause.
lets say pkey is your primary key and skey is your sort key, and
there exists an index on (skey,pkey)
your first select is
select ... from tab ORDER by skey,pkey LIMIT 100;
your subsequent selects are
select ... from tab WHERE skey>skey_last
OR (skey=skey_last AND pkey>pkey_last)
ORDER BY skey,pkey
LIMIT 100 OFFSET 100;
> I would appreciate any help, in fact this is my primary reason for
> joining this list ;-)
gnari
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Does Postgresql have a similar pseudo-column "ROWNUM" as
2005-05-12 19:07 Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? Dennis.Jiang@thomson.com
2005-05-17 17:06 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? Andrew Sullivan <ajs@crankycanuck.ca>
2005-05-17 17:48 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Alain <alainm@pobox.com>
2005-05-17 18:12 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Ragnar Hafstað <gnari@simnet.is>
2005-05-17 18:43 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Alain <alainm@pobox.com>
2005-05-17 22:03 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Ragnar Hafstað <gnari@simnet.is>
@ 2005-05-18 02:16 ` Alain <alainm@pobox.com>
2005-05-18 08:28 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Ragnar Hafstað <gnari@simnet.is>
0 siblings, 1 reply; 17+ messages in thread
From: Alain @ 2005-05-18 02:16 UTC (permalink / raw)
To: pgsql-sql
Ragnar Hafstað escreveu:
>>[how to solve the get next 100 records problem]
>
>>I tried that. It does not work in the generic case: 6 MegaRec, telephone
>>listing, alphabetical order.
>
> lets say pkey is your primary key and skey is your sort key, and
> there exists an index on (skey,pkey)
>
> your first select is
> select ... from tab ORDER by skey,pkey LIMIT 100;
>
> your subsequent selects are
> select ... from tab WHERE skey>skey_last
> OR (skey=skey_last AND pkey>pkey_last)
> ORDER BY skey,pkey
> LIMIT 100 OFFSET 100;
I tied that, it is veeery slow, probably due to the OR operand :(
BUT, I think that this is close to a final solution, I made some
preliminary test ok. Please tell me what you think about this.
Fisrt let's state that I am reading records to put on a screen (in a
Table/Grid). I separated the problem is *3* parts
-first select is as above:
select ... from tab ORDER by skey,pkey LIMIT 100;
-second method for next 100:
select ... from tab WHERE skey>=skey_last
ORDER BY skey,pkey
LIMIT 100;
but here I test for repetitions using pkey and discard them
-now if I get all repetitions or the last 100 have the same skey with
the second method, I use
select ... from tab WHERE skey=skey_last AND pkey>pkey_last
ORDER BY skey,pkey
LIMIT 100;
until I get an empty response, then I go back to the second method.
All queries are extremely fast with 6000000 records and it looks like
the few redundant or empty queries (but very fast) will not be a problem.
What is your opinion about this (apart that it is a bit complex :) ??
Alain
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Does Postgresql have a similar pseudo-column "ROWNUM" as
2005-05-12 19:07 Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? Dennis.Jiang@thomson.com
2005-05-17 17:06 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? Andrew Sullivan <ajs@crankycanuck.ca>
2005-05-17 17:48 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Alain <alainm@pobox.com>
2005-05-17 18:12 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Ragnar Hafstað <gnari@simnet.is>
2005-05-17 18:43 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Alain <alainm@pobox.com>
2005-05-17 22:03 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Ragnar Hafstað <gnari@simnet.is>
2005-05-18 02:16 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Alain <alainm@pobox.com>
@ 2005-05-18 08:28 ` Ragnar Hafstað <gnari@simnet.is>
0 siblings, 0 replies; 17+ messages in thread
From: Ragnar Hafstað @ 2005-05-18 08:28 UTC (permalink / raw)
To: Alain <alainm@pobox.com>; +Cc: pgsql-sql
On Tue, 2005-05-17 at 23:16 -0300, Alain wrote:
>
> Ragnar Hafstað escreveu:
> >>[how to solve the get next 100 records problem]
> BUT, I think that this is close to a final solution, I made some
> preliminary test ok. Please tell me what you think about this.
>
> Fisrt let's state that I am reading records to put on a screen (in a
> Table/Grid). I separated the problem is *3* parts
>
> -first select is as above:
> select ... from tab ORDER by skey,pkey LIMIT 100;
>
> -second method for next 100:
> select ... from tab WHERE skey>=skey_last
> ORDER BY skey,pkey
> LIMIT 100;
> but here I test for repetitions using pkey and discard them
>
> -now if I get all repetitions or the last 100 have the same skey with
> the second method, I use
> select ... from tab WHERE skey=skey_last AND pkey>pkey_last
> ORDER BY skey,pkey
> LIMIT 100;
> until I get an empty response, then I go back to the second method.
if your distribution is such that those skeys that have > 100 records
tend to have a lot more, you might have a higher limit for this case.
> All queries are extremely fast with 6000000 records and it looks like
> the few redundant or empty queries (but very fast) will not be a problem.
>
> What is your opinion about this (apart that it is a bit complex :) ??
looks fine
gnari
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Does Postgresql have a similar pseudo-column "ROWNUM" as
2005-05-12 19:07 Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? Dennis.Jiang@thomson.com
2005-05-17 17:06 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? Andrew Sullivan <ajs@crankycanuck.ca>
2005-05-17 17:48 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Alain <alainm@pobox.com>
@ 2005-05-18 19:52 ` Chris Browne <cbbrowne@acm.org>
2005-05-18 20:20 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Sean Davis <sdavis2@mail.nih.gov>
1 sibling, 1 reply; 17+ messages in thread
From: Chris Browne @ 2005-05-18 19:52 UTC (permalink / raw)
To: pgsql-sql
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 ;-).)
--
(format nil "~S@~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/;
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Does Postgresql have a similar pseudo-column "ROWNUM" as
2005-05-12 19:07 Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? Dennis.Jiang@thomson.com
2005-05-17 17:06 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? Andrew Sullivan <ajs@crankycanuck.ca>
2005-05-17 17:48 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Alain <alainm@pobox.com>
2005-05-18 19:52 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Chris Browne <cbbrowne@acm.org>
@ 2005-05-18 20:20 ` Sean Davis <sdavis2@mail.nih.gov>
0 siblings, 0 replies; 17+ messages in thread
From: Sean Davis @ 2005-05-18 20:20 UTC (permalink / raw)
To: Chris Browne <cbbrowne@acm.org>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Does Postgresql have a similar pseudo-column "ROWNUM" as
2005-05-12 19:07 Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? Dennis.Jiang@thomson.com
2005-05-17 17:06 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? Andrew Sullivan <ajs@crankycanuck.ca>
@ 2005-05-17 22:13 ` PFC <lists@boutiquenumerique.com>
2005-05-17 22:27 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Ragnar Hafstað <gnari@simnet.is>
1 sibling, 1 reply; 17+ messages in thread
From: PFC @ 2005-05-17 22:13 UTC (permalink / raw)
To: Ragnar Hafstað <gnari@simnet.is>; Alain <alainm@pobox.com>; +Cc: pgsql-sql
> your subsequent selects are
> select ... from tab WHERE skey>skey_last
> OR (skey=skey_last AND pkey>pkey_last)
> ORDER BY skey,pkey
> LIMIT 100 OFFSET 100;
why offset ?
you should be able to use the skey, pkey values of the last row on the
page to show the next page, no need for offset then.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Does Postgresql have a similar pseudo-column "ROWNUM" as
2005-05-12 19:07 Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? Dennis.Jiang@thomson.com
2005-05-17 17:06 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? Andrew Sullivan <ajs@crankycanuck.ca>
2005-05-17 22:13 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as PFC <lists@boutiquenumerique.com>
@ 2005-05-17 22:27 ` Ragnar Hafstað <gnari@simnet.is>
0 siblings, 0 replies; 17+ messages in thread
From: Ragnar Hafstað @ 2005-05-17 22:27 UTC (permalink / raw)
To: PFC <lists@boutiquenumerique.com>; +Cc: Alain <alainm@pobox.com>; pgsql-sql
On Wed, 2005-05-18 at 00:13 +0200, PFC wrote:
> > your subsequent selects are
> > select ... from tab WHERE skey>skey_last
> > OR (skey=skey_last AND pkey>pkey_last)
> > ORDER BY skey,pkey
> > LIMIT 100 OFFSET 100;
>
> why offset ?
> you should be able to use the skey, pkey values of the last row on the
> page to show the next page, no need for offset then.
of course you are right. the WHERE clause is supposed to replace
the OFFSET. too much cut and pasting without thinking and testing.
gnari
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2005-05-20 18:42 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-05-12 19:07 Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? Dennis.Jiang@thomson.com
2005-05-17 17:01 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Scott Marlowe <smarlowe@g2switchworks.com>
2005-05-20 18:27 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Keith Worthington <keithw@narrowpathinc.com>
2005-05-20 18:42 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Scott Marlowe <smarlowe@g2switchworks.com>
2005-05-17 17:06 ` Bruno Wolff III <bruno@wolff.to>
2005-05-17 17:06 ` Andrew Sullivan <ajs@crankycanuck.ca>
2005-05-17 17:48 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Alain <alainm@pobox.com>
2005-05-17 18:12 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Ragnar Hafstað <gnari@simnet.is>
2005-05-17 18:43 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Alain <alainm@pobox.com>
2005-05-17 20:11 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Andrew Sullivan <ajs@crankycanuck.ca>
2005-05-17 22:03 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Ragnar Hafstað <gnari@simnet.is>
2005-05-18 02:16 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Alain <alainm@pobox.com>
2005-05-18 08:28 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Ragnar Hafstað <gnari@simnet.is>
2005-05-18 19:52 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Chris Browne <cbbrowne@acm.org>
2005-05-18 20:20 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Sean Davis <sdavis2@mail.nih.gov>
2005-05-17 22:13 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as PFC <lists@boutiquenumerique.com>
2005-05-17 22:27 ` Re: Does Postgresql have a similar pseudo-column "ROWNUM" as Ragnar Hafstað <gnari@simnet.is>
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