H2 in PostgreSQL mode still behaves differently from PostgreSQL
SQL that runs on PostgreSQL fails on H2 in testsShort answer
H2 compatibility modes are switched on in the JDBC URL and cover only a small part of the differences between databases. Check that MODE=PostgreSQL is really in the URL your tests use, add DATABASE_TO_LOWER for name case, and keep the in-memory database open. For anything the mode lacks, test against real PostgreSQL.
Fix it fast
1Find out which cause you have
Print the JDBC URL the test really uses. Properties in a test profile or a Testcontainers setup can replace the one you edited.
grep -rn "jdbc:h2" src/2Match what you see, then jump to its fix
- 1
The same SQL works on PostgreSQL and fails on H2, and the URL has no MODE= partThe mode is missing from the URL that is usedFix - 2
Queries on quoted or unquoted names, or ORDER BY with nulls, give different results from PostgreSQLName case and null ordering settings are missingFix - 3
Tables created by the schema script are gone when the next connection opensThe in-memory database disappears between connectionsFix - 4
The mode is set, names are right, and one PostgreSQL specific statement or type still failsThe feature is not part of the compatibility modeFix
- 1
What does the error look like?
SQL that runs on PostgreSQL fails on H2 in testsNames or null ordering differ from PostgreSQL in test results
Cause 1 of 4
The mode is missing from the URL that is used
1Check
The H2 documentation sets the mode through the URL, for example MODE=PostgreSQL, or with the SQL statement SET MODE. In a @DataJpaTest slice Spring Boot configures its own embedded database when one is on the classpath, so your URL may never be used.
grep -rn "MODE=" src/
grep -rn "DataJpaTest\|AutoConfigureTestDatabase" src/test2Fix
Use the documented PostgreSQL URL form with its extra settings, and in a test slice tell Spring Boot to use your datasource instead of its own embedded one.
spring.datasource.url=jdbc:h2:mem:testdb;MODE=PostgreSQL;DATABASE_TO_LOWER=TRUE;DEFAULT_NULL_ORDERING=HIGH
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)3Verify
Run one query that failed before.
./mvnw -q testConfirmed by docs.spring.io
Cause 2 of 4
Name case and null ordering settings are missing
1Check
The H2 documentation gives the PostgreSQL URL as MODE=PostgreSQL together with DATABASE_TO_LOWER=TRUE and DEFAULT_NULL_ORDERING=HIGH. A URL with only the mode misses the other two.
grep -rn "DATABASE_TO_LOWER" src/2Fix
Use the full URL from the documentation. It warns not to change DATABASE_TO_LOWER after the database was created, so use a fresh in-memory name or delete the file database.
jdbc:h2:mem:testdb2;MODE=PostgreSQL;DATABASE_TO_LOWER=TRUE;DEFAULT_NULL_ORDERING=HIGH3Verify
Run the query that gave different results before.
./mvnw -q testConfirmed by h2database.com
Cause 3 of 4
The in-memory database disappears between connections
1Check
An in-memory database is dropped when its last connection closes, so a mode setting and the schema go with it.
grep -rn "DB_CLOSE_DELAY" src/2Fix
Add DB_CLOSE_DELAY=-1 to keep the database open as long as the JVM runs.
jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;MODE=PostgreSQL;DATABASE_TO_LOWER=TRUE3Verify
Run two tests in a row that both read the schema.
./mvnw -q testConfirmed by h2database.com
Cause 4 of 4
The feature is not part of the compatibility mode
1Check
H2 says it implements only a small subset of the differences between databases. Compare your failing statement with the PostgreSQL mode list on the H2 features page.
grep -rn "MODE=PostgreSQL" src/2Fix
Change the statement to plain standard SQL, or run those tests against a real PostgreSQL database by pointing the datasource at it and keeping the test database replacement off.
@AutoConfigureTestDatabase(replace = Replace.NONE)3Verify
The test passes on the database you actually deploy to.
./mvnw -q verifyConfirmed by h2database.com
How do I stop it coming back?
- Treat H2 as a fast approximation and run the important queries on real PostgreSQL before release.
- Keep one test database URL in one place.
- Write migrations in portable SQL where you can.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
Related fixes
Databases
Hibernate insert fails: column is of type bit but expression is of type boolean
ERROR: column "active" is of type bit but expression is of type boolean
PostgreSQL has a real boolean type and a separate bit type, and it will not convert one to the other on insert.
3 causes, each with a fixDatabases
Spring Boot cannot configure a DataSource for PostgreSQL
Failed to determine a suitable driver class
Spring Boot builds a pooled DataSource only when it has a JDBC URL and a loadable driver.
3 causes, each with a fixDatabases
Spring Boot stops at startup with flywayInitializer failing
Caused by: org.flywaydb.core.api.FlywayException
flywayInitializer is the bean that runs your migrations at startup, so any migration problem surfaces as its BeanCreationException.
4 causes, each with a fixDatabases
Spring Boot cannot use the PostgreSQL JDBC URL it was given
java.lang.RuntimeException: Driver org.postgresql.Driver claims to not accept jdbcUrl
The message means the URL string handed to the driver is not a PostgreSQL JDBC URL.
3 causes, each with a fixDatabases
Spring Boot JPA cannot find the PostgreSQL table it maps to
ERROR: relation "telephone_number" does not exist
Spring Boot maps an entity to a lowercase snake_case table name and creates tables on its own only for embedded databases.
2 causes, each with a fixDatabases
Flyway cannot handle the PostgreSQL URL after upgrading to Flyway 10 or newer
No database found to handle jdbc:postgresql://localhost:5432/app
Since Flyway 10, each database has its own module.
4 causes, each with a fix