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?

Hey Nelio, did you find any solution for that? I'm facing the same problem — Thiago Cavalcanti
– Thiago Cavalcanti, Commented May 12, 2021 at 21:46

Arthur Fuzaro · Accepted Answer · 2021-05-13 20:23:47Z

2

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 :)

edited May 13, 2021 at 20:23

answered May 13, 2021 at 16:38

Arthur Fuzaro

536 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thiago Cavalcanti Over a year ago

You're a life savior!! Simple and ellegant. Thanks man

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related