Thread: Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle?
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
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;
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
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.
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
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
Hi
I'm trying to insert encrypted data into the database and I'm noticing
error dealing with quotes. Below is the error print out...
suggestions and/or at least point me in the direction to find a solution,
Thanks,
J
INSERT INTO sample.users (user_name, first_name) VALUES
('jokers', '=ïµiF!¶6(ÖŸã�¾óˆÌ‘'-Iw‰iDÖiJÐÿ† %')
Warning: pg_query() [function.pg-query]: Query failed: ERROR:
unterminated quoted string at or near "'=ïµi" at character 68 in
/usr/local/apache2/htdocs/php/5/Theirry_DB.php on line 162
On Tue, 2005-05-17 at 13:15, Postgres Admin wrote:
> Hi
>
> I'm trying to insert encrypted data into the database and I'm noticing
> error dealing with quotes. Below is the error print out...
>
> suggestions and/or at least point me in the direction to find a solution,
>
> Thanks,
> J
>
>
>
> INSERT INTO sample.users (user_name, first_name) VALUES
> ('jokers', '=ïµiF!¶6(ÖŸã¾óˆÌ‘'-Iw‰iDÖiJÐÿ† %')
>
> Warning: pg_query() [function.pg-query]: Query failed: ERROR:
> unterminated quoted string at or near "'=ïµi" at character 68 in
> /usr/local/apache2/htdocs/php/5/Theirry_DB.php on line 162
Use a bytea field and use pg_escape_bytea() to prepare the data for
insertion.
Scott Marlowe wrote: > >Use a bytea field and use pg_escape_bytea() to prepare the data for >insertion. > > > Thanks Scott, I will try it now. J
>>> >>>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
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
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
> 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.
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
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 LIMIT100; 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
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
Hi Ragnar (and others), I found something that is both fast and simple (program side): Allways assuming that: pkey is a primary key and skey is a sort key, and there exists an index on (skey,pkey) first select is select ... from tab ORDER by skey,pkey LIMIT 100; subsequent selects are (select ... from tab WHERE skey=skey_last AND pkey>pkey_last ORDER BY skey,pkey LIMIT 100) UNION (select ... from tab WHERE skey>skey_last ORDER BY skey,pkey LIMIT 100) ORDER BY skey,pkey LIMIT 100; The catch is that if the first select would have more than 100 records and was limited to 100, the second select's data is completeply discarted by the 3rd limit! The only strange thing is that without the 3rd order by, the order is wrong. I didn't expect it because each select is created ordered. Is it expected that UNION mixes it all up? (using postgre 7.4.1) The 3rd order by is not indexed, but it operates in a memory table of no more than 200 so it is fast too. Please comment on this. I tested and it worked but I really new to sql and I feel insecure... Thanks, Alain >> >>>>[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 > > > >
> The only strange thing is that without the 3rd order by, the order is > wrong. I didn't expect it because each select is created ordered. Is it > expected that UNION mixes it all up? (using postgre 7.4.1) That's because UNION removes duplicates, which it will probably doing using a hash (EXPLAIN ANALYZE is your friend).Use UNION ALL because your WHERE condition allows no duplicates anyway. UNION ALL preserves the order.So you can get the LIMIT out of the subqueries and put it around the UNION ALL.
On Wed, 2005-05-18 at 12:42 -0300, Alain wrote: > I found something that is both fast and simple (program side): > ... > subsequent selects are > (select ... from tab WHERE skey=skey_last AND pkey>pkey_last > ORDER BY skey,pkey LIMIT 100) > UNION > (select ... from tab WHERE skey>skey_last > ORDER BY skey,pkey LIMIT 100) > ORDER BY skey,pkey LIMIT 100; > ... > The only strange thing is that without the 3rd order by, the order is > wrong. I didn't expect it because each select is created ordered. Is it > expected that UNION mixes it all up? (using postgre 7.4.1) ORDER BY on subselects are not garanteed by SQL to have any effect, but Postgres tends to do what you want when possible and not detrimental to performance. In this case, Postgres would probably have kept the order had you used UNION ALL a plain UNION implies DISTINCT, which Postgres is free to implement any way it wants, possibly destroying the order in this case a UNION ALL is appropriate, as you know that the 2 selects do not overlap. possibly, a future version of the planner will be able to detect this. in any case, the last ORDER BY LIMIT does not cost much, and it protects you against implementation changes, and limits the result to 100 records, which might be what you want. > Please comment on this. I tested and it worked but I really new to sql > and I feel insecure... it's good. gnari
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/>
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
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_typeAS 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
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. :)