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] <javascript:>> 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] <javascript:>. >> To post to this group, send email to [email protected] >> <javascript:>. >> 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.
