Hibernate fail with Postgresql : Row comparison operators must be associated with btree operator families.

Explanation of the problem

Recently I had the following issue with PostgreSQL ( 10+) :

SQL exception            : Exception while executing meta query: ERROR: could not determine interpretation of row comparison operator ~
  Hint: Row comparison operators must be associated with btree operator families.
  Position: 1119

Basically, the origin of the problem is linked with a JQPL query :

String qry = "from jpmi.model.compte.Pension p where p.numeroDePension IN :ndp";

numeroDePension field is an @Embeddable field so we are comparing a row with a multi-column operator.

Hibernate is generating the following code ( 5+)

 select ... from d_pension pension0_ where (pension0_.cle, pension0_.compte, pension0_.millesime, pension0_.sequence) like (?, ?, ?, ?)

We see the multi-column comparison that is not supported.

Solutions

So you have the following solutions :

• Replacing the LIKE operator by = or IN in the JPQL query.
• Create a custom operator in PostgreSQL
• Replace the @Embeddable comparison with a field by field comparison

The article helped to find the solution https://github.com/jOOQ/jOOQ/issues/8072.