How to Fix H2 Tests Passing But PostgreSQL Failing in Production

Mar 9, 2026

My integration tests passed. All 127 of them. Green checkmarks everywhere.

Then I deployed to production. Flyway migrations failed. The error message made no sense - the same migrations worked perfectly in my test environment.

org.postgresql.util.PSQLException: ERROR: syntax error at or near "WHERE"
  Position: 45

I stared at my migration file. It looked correct. I had tested it with H2. Why did PostgreSQL reject it?

The Root Cause

I was using H2 in PostgreSQL compatibility mode for testing. My configuration looked like this:

spring:
  datasource:
    url: jdbc:h2:mem:testdb;MODE=PostgreSQL;DATABASE_TO_LOWER=TRUE;DEFAULT_NULL_ORDERING=HIGH
    driver-class-name: org.h2.Driver

I thought this would make H2 behave like PostgreSQL. It doesn’t.

H2’s PostgreSQL mode is an approximation, not an exact replica. It supports PostgreSQL syntax on the surface but differs in:

Constraint handling
Index behavior (especially partial indexes)
SQL function implementations
Transaction isolation levels

My tests validated against H2’s interpretation of SQL, not PostgreSQL’s actual behavior.

What Broke: The Partial Index Problem

The failing migration contained a partial unique index:

CREATE UNIQUE INDEX idx_unique_active_email
ON users (email)
WHERE is_active = TRUE;

This is valid PostgreSQL syntax. It creates a unique index that only applies to active users. Inactive users can have duplicate emails.

H2 doesn’t fully support partial indexes. In H2’s PostgreSQL mode, this statement either:

Fails silently (creates index without WHERE clause)
Throws an error (depends on H2 version)

My test passed because H2 handled it one way. PostgreSQL handled it correctly, but my migration had other issues I hadn’t caught.

Other Differences I Encountered

After investigating, I found several more discrepancies.

Constraint Behavior

PostgreSQL enforces constraints differently than H2. I had a foreign key constraint that behaved differently:

-- This works in H2 but fails in PostgreSQL with certain data
ALTER TABLE orders
ADD CONSTRAINT fk_user
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE SET NULL;

H2 allowed this with existing data. PostgreSQL required a check first.

Data Type Differences

I used TEXT in migrations. H2 treated it as VARCHAR. PostgreSQL has distinct TEXT and VARCHAR types with different performance characteristics.

Function Availability

Some PostgreSQL functions don’t exist in H2:

-- PostgreSQL-specific function
SELECT * FROM users
WHERE created_at &lt; NOW() - INTERVAL '1 day';

H2 requires different syntax for intervals:

-- H2 syntax
SELECT * FROM users
WHERE created_at &lt; DATEADD('DAY', -1, CURRENT_TIMESTAMP);

The Solution: Test with Real PostgreSQL

I switched to Testcontainers for integration testing. This spins up an actual PostgreSQL instance for each test run.

&lt;dependency&gt;
    &lt;groupId&gt;org.testcontainers&lt;/groupId&gt;
    &lt;artifactId&gt;postgresql&lt;/artifactId&gt;
    &lt;version&gt;1.19.3&lt;/version&gt;
    &lt;scope&gt;test&lt;/scope&gt;
&lt;/dependency&gt;

@SpringBootTest
@Testcontainers
class MigrationIntegrationTest {

    @Container
    static PostgreSQLContainer&lt;?&gt; postgres = new PostgreSQLContainer&lt;&gt;("postgres:15-alpine")
        .withDatabaseName("testdb");

    @DynamicPropertySource
    static void configureProperties(DynamicPropertyRegistry registry) {
        registry.add("spring.datasource.url", postgres::getJdbcUrl);
        registry.add("spring.datasource.username", postgres::getUsername);
        registry.add("spring.datasource.password", postgres::getPassword);
    }

    @Test
    void flywayMigrationsShouldSucceed() {
        // Now testing against REAL PostgreSQL
    }
}

The same migration that passed in H2 now failed immediately. I caught the problem before deployment.

Performance Considerations

Testcontainers adds overhead. Each test run starts a Docker container.

For my team, this added about 15 seconds to the test suite. Acceptable for catching production bugs.

If you need faster feedback during development, consider a hybrid approach:

Use H2 for unit tests (simple CRUD, no database-specific features)
Use Testcontainers PostgreSQL for integration tests (migrations, complex queries)

// Fast unit tests with H2
@ActiveProfiles("unit")
@ExtendWith(SpringExtension.class)
class UserRepositoryUnitTest {
    // Uses H2 for speed
}

// Integration tests with real PostgreSQL
@SpringBootTest
@Testcontainers
@ActiveProfiles("integration")
class MigrationIntegrationTest {
    // Uses Testcontainers for accuracy
}

What About Flyway Validation?

Flyway has a validateOnMigrate option. I enabled it:

spring:
  flyway:
    validate-on-migrate: true
    out-of-order: false

This checks migrations before applying them. But it validates against the connected database. With H2, it validates against H2’s interpretation. Not helpful.

The real fix: validate against the same database you use in production.

Lessons Learned

I made three mistakes:

Mistake 1: Assuming H2 PostgreSQL mode was “close enough”

It isn’t. H2 approximates PostgreSQL syntax but doesn’t replicate behavior. The differences matter.

Mistake 2: Using PostgreSQL-specific features without testing against PostgreSQL

Partial indexes, advanced constraints, PostgreSQL functions - these need real PostgreSQL testing.

Mistake 3: Skipping production-like testing before deployment

I relied entirely on H2 tests. When production differed, everything broke.

When H2 Is Still Useful

I still use H2 for early development. It’s fast. It’s convenient. It works for:

Rapid prototyping
Simple CRUD operations
Learning Spring Boot basics

But before any production deployment, I now run tests against real PostgreSQL.

Summary

H2 tests pass but PostgreSQL fails because H2’s PostgreSQL mode doesn’t perfectly replicate PostgreSQL behavior. The solution is straightforward: test against the database you use in production.

Use Testcontainers to spin up real PostgreSQL instances for integration tests. Catch dialect differences, constraint behavior issues, and PostgreSQL-specific feature incompatibilities before deployment.

If you use H2 for early development, understand its limitations. Switch to PostgreSQL testing before production.

Final Words + More Resources

My intention with this article was to help others share my knowledge and experience. If you want to contact me, you can contact by email: Email me

Here are also the most important links from this article along with some further resources that will help you in this scope:

👨‍💻 H2 Database PostgreSQL Compatibility Mode
👨‍💻 Testcontainers PostgreSQL Module
👨‍💻 PostgreSQL Partial Indexes Documentation
👨‍💻 Flyway Migration Validation

Oh, and if you found these resources useful, don’t forget to support me by starring the repo on GitHub!