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.

Reply via email to