Hi, Yes if there is an update the row is moved at the end, like the sample I post. This is why some database gui frontend force the sort of a column. Maybe the implementation of H2 make such feature request nearly impossible to accomplish ?
-Nicolas Le lundi 13 octobre 2014 20:04:59 UTC+2, Thomas Mueller a écrit : > > Hi, > > Does PostgreSQL makes a guarantee that two separate queries will use the > same row number for the same logical row? I think that's an implementation > detail. If there is a update between those calls (for example by another > connection), the row number might be different. > > Regards, > Thomas > > > > On Thursday, October 9, 2014, Nicolas Fortin (OrbisGIS) < > [email protected] <javascript:>> wrote: > >> Hi Thomas, >> >> Here a complete example of the difference between Postgre and H2: >> >> [raw] >> drop table if exists streets; >> create table streets(pk int primary key, length real); >> insert into streets values (5, 55.2), (7, 33.2), (9, 151.1), (15, 48.2), >> (20, 14.6); >> update streets set length = 60 where pk = 5; >> >> H2: >> Execute request 5/6: select rowcpt, pk, length from (select ROW_NUMBER() >> OVER () rowcpt, pk ,length from streets) streetscount where length > 40 >> >> ROWCPT(INTEGER) PK(INTEGER) >> LENGTH(REAL) >> 1 5 >> 60 >> 2 9 151,1 >> 3 15 48,2 >> >> Execute request 6/6: select * from streets >> >> PK(INTEGER) LENGTH(REAL) >> 5 60 >> 7 33,2 >> 9 151,1 >> 15 48,2 >> 20 14,6 >> >> Postgre: >> >> Execute request 5/6: select rowcpt, pk, length from (select ROW_NUMBER() >> OVER () rowcpt, pk ,length from streets) streetscount where length > 40 >> >> rowcpt(int8) pk(int4) >> length(float4) >> 2 9 151,1 >> 3 15 48,2 >> 5 5 >> 60 >> >> Execute request 6/6: select * from streets >> >> pk(int4) length(float4) >> 7 33,2 >> 9 151,1 >> 15 48,2 >> 20 14,6 >> 5 60 >> [/raw] >> >> If a show a table of the full content of streets, thanks to Postgre >> without caching primary key I could highlight the first, third and fourth >> lines. >> >> Following the result of "select * from streets", I expect h2 returning >> rowcpt 1,3,4 >> >> We have decided to cache primary key (take time and memory for big table) >> in order to bypass this problem. >> >> Thanks for support. >> >> - Nicolas >> IRSTV FR CNRS 2488 >> >> Le jeudi 9 octobre 2014 07:52:38 UTC+2, Thomas Mueller a écrit : >>> >>> Hi, >>> >>> I'm not sure if I understand. You basically need a row id, which >>> uniquely identifies a row within a table? H2 supports the _rowid_ column >>> (like SQLite). Wouldn't that be enough for you? Is the problem that the >>> syntax (when using _rowid_) would be different to PostgreSQL? >>> >>> Maybe it's easier if you could provide a complete example (as small as >>> possible, but including "create table" and "insert" statements), that shows >>> what you need. >>> >>> Regards, >>> Thomas >>> >>> >>> >>> >>> >>> >>> On Wed, Oct 8, 2014 at 4:50 PM, Nicolas Fortin (OrbisGIS) < >>> [email protected]> wrote: >>> >>>> To go further I have to find a way to guess if TopTableFilter belongs >>>> to another preparedStatement. The only difference that I found is the >>>> Select.command is null in sub query. >>>> >>>> -- >>>> You received this message because you are subscribed to the Google >>>> Groups "H2 Database" group. >>>> To unsubscribe from this group and stop receiving emails from it, send >>>> an email to [email protected]. >>>> To post to this group, send email to [email protected]. >>>> Visit this group at http://groups.google.com/group/h2-database. >>>> For more options, visit https://groups.google.com/d/optout. >>>> >>> >>> -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
