I often find myself in a situation where I want to write automated tests for my database queries. My current use case is a Spring MVC web app and a Postgres database. As far as I can tell, I have two options:
Stand up an actual Postgres instance and initialize it with a test data set Utilize an in-memory database such as H2. I have used Option #1 in the past (albeit with an Oracle database and Oracle XE) with good success. The main downfall of this approach is that the tests aren't self contained. In order to successfully run the Integration Tests, you'd need to actually setup the database environment and have it accessible from wherever the tests are run. The H2 solution is more attractive in this respect because the configurations are all contained within the project.
Unfortunately, I have come across a scenario where the H2 database (in Postgres mode) behaves differently than an actual Postgres instance.
@Query("SELECT p FROM Project p WHERE "
+ "(:state is NULL or p.state = :state) and "
+ "(:projectType is NULL or p.projectType = :projectType))")
The purpose of this query is to treat the two parameters (state and projectType) as optional. If the client supplies parameters with a concrete value, then return records with that value. If not, then return all records.
This query works with H2, but fails in the actual Postgres instance:
org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying = bytea Hint:
No operator matches the given name and argument type(s). You might need to add explicit type casts.
Now I worry I can't trust my Integration Tests. The entire purpose of this work is to test that my SQL is correct. But now I can't trust that my SQL is correct even if all of my tests pass.
My question is does anyone know why this error is occurring in Postgres 9 and not H2 in Postgres mode?
