Can you help in this issue,

https://stackoverflow.com/questions/74957058/issues-in-spring-boot-with-hibernate-using-h2-for-testing-simulating-postgresql

i have did declaration of teh mode=PostgreSQL in many ways, but not working 

 spring:
   datasource:
     url: jdbc:h2:mem:data-service-mem;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=false;**MODE=PostgreSQL**

i'm running my test with @SpringBootTest and @AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE), but not working,

The query is working well in the h2 console when i add MODE=PostgreSQL, to the url

the qury is 

    update deal stdeal set sub_product_type = maxleg.sub_product_type, product_type=stdeal.sub_product_type 
        from (
            select row_number() over(partition by structure_id order by leg_id desc) as rownumber
                , structure_id
                , sub_product_type 
            from deal 
            where 
                and product_type = 'structure' 
                and sub_product_type not in ('vp', 'premium')
        ) maxleg 
where stdeal.structure_id = maxleg.structure_id 
            and stdeal.product_type = 'structure' 
            and stdeal.sub_product_type in ('vp', 'premium') and rownumber = 1

Spring-Boot : 2.6.9

Comment From: wilkinsona

@rboughani It's hard to say what may be happening, particularly as the Stack Overflow question doesn't include a minimal, reproducible example. If you have the exact same problem (a missing table), it's unlikely to be due to a bug in Spring Boot as Hibernate is typically responsible for creating the database schema. It could be a bug in Hibernate or it could be a bug in H2's emulation of Postgres.

As mentioned in the guidelines for contributing, we prefer to use GitHub issues only for bugs and enhancements so the best way to get some help would be to ask a question of your own on Stack Overflow that provides a minimal reproducible example.

Comment From: MaximLopatin

I think I have the same problem. The migration step, executed directly on the H2 database, fails until I activate the PostgreSQL mode. After that, all statements are executed correctly.

However, when I add the mode to the spring.datasource.url like so: url: jdbc:h2:mem:errordb;mode=postgresql

I still get the same error messages as when I interact directly with H2 without the PostgreSQL mode. In the attachment you can find minimal example reproducing the error.

h2mode.tar.gz

Comment From: wilkinsona

@MaximLopatin I am not sure that it is the same problem. From what I can tell, you aren't using Hibernate to create the database schema yet in the question on Stack Overflow that's linked to above, that's where the failure is occurring.

In this case I think your SQL is invalid. H2, even in PostgreSQL compatibility mode, requires START WITH not just START. See https://github.com/h2database/h2database/issues/3337#issuecomment-1006431968.

Your application starts with this migration:

CREATE SEQUENCE batch_job_execution_seq
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 9223372036854775807
    START WITH 1
    CACHE 1
    NO CYCLE;

Comment From: MaximLopatin

thank you Andy for fast response. Indeed if i correct the migration the error disappears. What i do not understand is, why the migration routine worked in spring boot <= 2.7 and postgresql with h2(20230704) in postgres mode allow the sql.