"operator does not exist: uuid = bytea" Java with Postgres

Problem:

Hibernate should map the java UUID type to the postgress uuid type. However, if hibernate does not know how to map this, it will just try to serialize the object, resulting in a byte[]. Of course, this just moves the issue to the database level. Values of the uuid postgress type cannot just be compared with byte array type.

PSQLException: ERROR: operator does not exist: uuid = bytea

Possible cause:

I encountered this issue when migrating from Spring Boot 1.x to Spring Boot 2.3.0. In Spring Boot 1.x it was sufficient for me to mark my id fields with @Id and to make them of java type UUID.

Quick and dirty solutions:

A possible solution, is to explicitly state the PSQL type for the id field.

 @Type(type="org.hibernate.type.PostgresUUIDType")
 @Id
 private UUID id;

A better solution, would be to define a system-wide replacement. You could put this declaration on any class or on a package. Defining it just once somewhere impacts all declarations of UUID actually.

 @TypeDef(name="postgres-uuid",
          defaultForType = UUID.class,
          typeClass = PostgresUUIDType.class)

Real solution:

Take a look in your log file, and you may see something like this. Double check the version of this dialect, and see if it matches with the one which you defined in your property files:

Dialect - HHH000400: Using dialect: org.hibernate.dialect.PostgreSQL81Dialect

In that case, be aware that the following property is outdated:

hibernate.dialect=org.hibernate.dialect.PostgreSQL9Dialect

Some of the hibernate properties now need to have a spring.jpa.properties prefix. So, in this case, the new property path should be spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQL9Dialect.

Which is the point where everything starts to make sense. This dialect does all required type definitions for you.

I solve my problem with this form: I utilized the command CAST for my UUID fields

  public List<CivilRecord> dashboardSearch(CivilRecordSearch civilRecordSearch)
      throws MessageException {
    SearchValidation.validateDashboardSearch(civilRecordSearch);
    List<CivilRecord> l = new ArrayList<>();
    try {
      StringBuilder query = new StringBuilder();
      //query.append("select c.id_civil_record\\:\\:text,c.nm_request,c.nm_rg,c.tx_name,c.dt_register,c.bl_priority ");
      query.append("select CAST(c.id_civil_record as text),c.nm_request,c.nm_rg,c.tx_name,c.dt_register,c.bl_priority ");
      query.append("from sc_civil.tb_civil_record c ");
      query.append("inner join sc_civil.tb_workflow_record w ");
      query.append("on w.id_civil_record = c.id_civil_record ");
      query.append("left join sc_civil.tb_lock l ");
      query.append("on l.id_record = c.id_civil_record ");
      query.append("where c.id_site = :idSite ");

      if (civilRecordSearch.getPriority() == null || civilRecordSearch.getPriority().equals(false))
        query.append("and c.bl_priority = :priority ");
      query.append("and c.bl_canceled = :canceled ");
      query.append("and w.id_type_workflow = :idTypeWorkflow ");
      query.append("and w.id_type_status_workflow = :idTypeStatusWorkflow ");

      query.append("and (l is null or l.id_user = CAST(:idUser AS uuid)) ");

      if (!StringUtils.isEmpty(civilRecordSearch.getName()))
        query.append("and c.tx_name ilike :name ");
      if (!StringUtils.isEmpty(civilRecordSearch.getRg()))
        query.append("and c.nm_rg like :rg ");

      if (civilRecordSearch.getRequestNumber() != null)
        query.append("and c.nm_request = :request ");

      query.append("order by c.bl_priority desc, c.dt_register ");

      Query q = em.createNativeQuery(query.toString());
      q.setParameter("idSite", civilRecordSearch.getSite().getId());
      if (civilRecordSearch.getPriority() == null || civilRecordSearch.getPriority().equals(false))
        q.setParameter("priority", false);
      q.setParameter("idTypeWorkflow", civilRecordSearch.getTypeworkflow().getId());
      q.setParameter("idTypeStatusWorkflow", civilRecordSearch.getTypestatusworkflow().getId());
      q.setParameter("idUser", civilRecordSearch.getIdUser().toString());
      q.setParameter("canceled", false);
      if (!StringUtils.isEmpty(civilRecordSearch.getName()))
        q.setParameter("name","%" + civilRecordSearch.getName() + "%");
      if (civilRecordSearch.getRequestNumber() != null)
        q.setParameter("request", civilRecordSearch.getRequestNumber());
      if (!StringUtils.isEmpty(civilRecordSearch.getRg()))
        q.setParameter("rg", civilRecordSearch.getRg());

      q.setMaxResults(maxResult);
      List<Object[]> lo = q.getResultList();
      em.clear();
      for(Object[] o : lo){
          CivilRecord c = new CivilRecord();
          c.setIdCivilRecord(UUID.fromString((String)o[0]));
          c.setRequest(((BigInteger)o[1]).longValue());
          c.setRg((String)o[2]);
          c.setName((String)o[3]);
          c.setRegister((Date)o[4]);
          c.setPriority(TypeYesNo.getByKey(((Boolean)o[5]).booleanValue()));
          c.setWorkflowRecords(findStatus(c.getIdCivilRecord()));
          l.add(c);
      }
      return l;
    } catch (Exception e) {
      log.severe(e.getMessage());
      throw e;
    }
  }