Postgres and Spring Data JPA: operator does not exist: bytea = bigint

Question

I have a N-N Product-Category model, and I've made a Spring Data JPA @Query to retrieve products of a given category (or all products if category is null):

@Query("SELECT DISTINCT obj FROM Product obj INNER JOIN obj.categories cats WHERE "
    + "(:category IS NULL OR :category IN cats)")
Page<Product> find(Category category, Pageable pageable);

It works perfectly in H2 database. However, when testing in Postgres 12, if a null category is given, I get the following error:

org.postgresql.util.PSQLException: ERROR: operator does not exist: bytea = bigint
  Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

Coalesce saved me once in the past for similar issue with a parameter of type Instant, but this time it did NOT solve the problem for an entity parameter:

    + "(COALESCE(:category, NULL) IS NULL OR :category IN cats)")

Just in case, I've tested only for NULL and it doesn't throw any errors for both null and not null categories:

    + "(:category IS NULL")

I've also tested only the IN clause, and got the same "operator does not exist" error when a null category is given:

    + "(:category IN cats)")

Tests have shown that, apparently, the :category IS NULL condition before OR operator is not preventing the :category IN cats of being evaluated. How can I solve this problem?

Answer 1

After facing the same problem as you and tried several different solutions I found one that works fine with postgreSQL.

Instead passing the list as null you can pass the list as a emptyList()

So I add a Ternary at my code to validate if the parameter is null if so I pass a Collections.emptyList();

Hope this help you too :)