Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

H2 in PostgreSQL mode still behaves differently from PostgreSQL

ErrorSQL that runs on PostgreSQL fails on H2 in tests

Short 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.

  • Applies to H2 2.x, Spring Boot 3.x, Java 21
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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/
  2. 2Match what you see, then jump to its fix

    1. 1The 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. 2Queries on quoted or unquoted names, or ORDER BY with nulls, give different results from PostgreSQLName case and null ordering settings are missingFix
    3. 3Tables created by the schema script are gone when the next connection opensThe in-memory database disappears between connectionsFix
    4. 4The mode is set, names are right, and one PostgreSQL specific statement or type still failsThe feature is not part of the compatibility modeFix

What does the error look like?

  • SQL that runs on PostgreSQL fails on H2 in tests
  • Names 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/test

2Fix

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 test

Confirmed by docs.spring.io

Did this fix it?

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=HIGH

3Verify

Run the query that gave different results before.

./mvnw -q test

Confirmed by h2database.com

Did this fix it?

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=TRUE

3Verify

Run two tests in a row that both read the schema.

./mvnw -q test

Confirmed by h2database.com

Did this fix it?

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 verify

Confirmed by h2database.com

Did this fix it?

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

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.