Spring application with PostgreSQL throwing "PSQLException: ERROR: operator does not exist: character = uuid"

Question

I have an entity that looks like this:

import lombok.*;
import org.hibernate.annotations.GenericGenerator;
import org.hibernate.annotations.Type;

import javax.persistence.*;
import java.util.List;
import java.util.UUID;

@Entity(name = "EnterpriseGroup")
@Data
@EqualsAndHashCode
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Table(name = "enterprise_groups")
public class EnterpriseGroup {

    @Id
    @GeneratedValue(generator = "uuid4")
    @GenericGenerator(name = "UUID", strategy = "uuid4")
    @Type(type = "pg-uuid")
    @Column(columnDefinition = "CHAR(36)")
    private UUID groupId;

    @Column(unique = true)
    private String name;

    private String description;

    @ManyToMany(fetch = FetchType.EAGER, cascade = {CascadeType.PERSIST})
    @JoinTable(
            name = "groups_roles",
            joinColumns = {@JoinColumn(name = "groupId")},
            inverseJoinColumns = {@JoinColumn(name = "roleId")})
    private List<UserRole> roles;
}

I have a default JPA Repository:

import com.fadata.security.domain.entities.EnterpriseGroup;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import java.util.UUID;

@Repository
public interface EnterpriseGroupRepository extends JpaRepository<EnterpriseGroup, UUID> {
}

When I try calling repository.findAll() method, I get the following exception:

o.h.engine.jdbc.spi.SqlExceptionHelper: could not extract ResultSet [n/a]
org.postgresql.util.PSQLException: ERROR: operator does not exist: character = uuid

Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

I've tried changing up the UUID type and generation strategies, the column definitions and name, and nothing has worked so far. I tried debugging it by putting a breakpoint on the repository method, but it is never hit, meaning there is some kind of validation that goes on before the method is hit, and that's where this issue originates from. I'm certain I'm passing a valid UUID, because a proper exception is thrown if I pass an invalid UUID format. Yet the exception I get makes me think there is some kind of issue with the way the UUID I pass in with the request is converted and hits the actual app. Any ideas are welcome! Thanks!

Replace pg-uuid with org.hibernate.type.PostgresUUIDType or ditch the type altogether and use the native postures UUID type for the column instead of a varchar(36). And ditch the @Repository on the interface it adds nothing and only hurts the eyes. — M. Deinum
– M. Deinum, Commented Jul 4, 2022 at 13:43
@K.Nikita Hibernate version is 5.6.8.Final, and hibernate.dialect is org.hibernate.dialect.PostgreSQLDialect — Hristo Naydenov
– Hristo Naydenov, Commented Jul 4, 2022 at 14:11

K.Nikita · Accepted Answer · 2022-07-05 01:19:57Z

1

Try it UUIDCharType link

@Id
@Type(type = "org.hibernate.type.UUIDCharType")
@GeneratedValue(generator = "UUID")
@GenericGenerator(
        name = "UUID",
        strategy = "org.hibernate.id.UUIDGenerator"
)
@Column(name = "groupId", updatable = false, nullable = false)
private UUID groupId;

Note: not need generate UUID by UUID.randomUUID()

answered Jul 5, 2022 at 1:19

K.Nikita

6195 silver badges13 bronze badges

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

2 Comments

Hristo Naydenov Over a year ago

Thanks, this seems to work. I had to remove the @Column annotation though, because it threw an exception that the column contained some null values for some reason. Upon testing it everything seems to work well now. I'm a bit confused as to why it didn't work with my initial configuration, and also with the type set to org.hibernate.type.PostgresUUIDType

K.Nikita Over a year ago

You can leave annotation @Column. Just need delete "nullable = false". Because you have entries with null groupId, you got error. PostgresUUIDType is work, but you defined column like CHAR @Column(columnDefinition = "CHAR(36)") it is not PostgresUUIDType (postgresql.org/docs/current/datatype-uuid.html)

Collectives™ on Stack Overflow

Spring application with PostgreSQL throwing "PSQLException: ERROR: operator does not exist: character = uuid"

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related