How to Fix PSQLException: Operator Does Not Exist: Character Varying = UUID

1. Introduction

In this tutorial, we’ll explore the PSQLException error: “operator does not exist: character varying = uuid” when using JPA to interact with PostgreSQL. We’ll delve into why this error occurs, identify common scenarios that trigger it, and see how to resolve it.

2. Causes of Exception

PostgreSQL distinguishes between character varying (string) and UUID data types. This distinction requires explicit type casting or conversion for comparisons between these types. Therefore, when we try to directly compare a UUID value with a string (VARCHAR) column, PostgreSQL raises an exception because it lacks an operator for this specific type of comparison.

Let’s consider an example where we have a User entity with a varchar column uuid:

@Entity
@Table(name = "User_Tbl")
public class User{
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(columnDefinition = "varchar")
    private UUID uuid;

    // getters and setters
}

When we try to query the database with a UUID value, we get the error:

UUID testId = UUID.fromString("c3917b5b-18ed-4a84-a6f7-6be7a8c21d66");
User user = new User();
user.setUuid(testId);
user.setName("John Doeee");
userRepository.save(user);

Throwable throwable = assertThrows(InvalidDataAccessResourceUsageException.class,
  () -> userRepository.findByUuid(testId),
  "Expected ERROR: operator does not exist: character varying = uuid");
assertTrue(getRootCause(throwable) instanceof PSQLException);

3. Fix the Exception

To resolve this issue, we need to ensure that comparisons between character varying and UUID values are handled correctly. Let’s look at the recommended approaches.

3.1. Using CAST() Function

We can use the CAST() function in PostgreSQL to explicitly cast the UUID value to a string within our JPA query.

The CAST() function is a built-in PostgreSQL function that allows us to convert a value of one data type to another. This ensures that the comparison between the UUID value and varchar types is handled correctly by PostgreSQL.

Here’s an example of how to fix the query:

@Query("SELECT u FROM User u WHERE u.uuid = CAST(:uuid AS text)")
Optional<User> findByUuidWithCastFunction(@Param("uuid") UUID uuid);

By casting the character varying value to a UUID, we ensure that the database can perform the comparison correctly:

UUID testId = UUID.fromString("c3917b5b-18ed-4a84-a6f7-6be7a8c21d66");

Optional<User> userOptional = userRepository.findByUuidWithCastFunction(testId);
assertThat(userOptional.isPresent(), is(true));
assertThat(userOptional.get().getUuid(), equalTo(testId));

Let’s observe the generated SQL query including the CAST(:id AS text) operation:

Hibernate: 
    select
        user0_.id as id1_0_,
        user0_.name as name2_0_,
        user0_.uuid as uuid3_0_ 
    from
        user_tbl user0_ 
    where
        user0_.uuid=cast(? as text)

The cast(? as text) shows the usage of the CAST() function to convert the UUID parameter to a text type, ensuring compatibility with the varchar column in PostgreSQL.

3.2. Using a Custom Converter

In addition to using direct casting with SQL functions, we can use the @Converter annotation in JPA to define a custom converter for the UUID object and varchar column. This converter facilitates the seamless conversion between UUID objects and their string representations in the database.

First, let’s implement the UUIDConverter class that implements AttributeConverter<UUID, String>:

@Converter
public class UUIDConverter implements AttributeConverter<UUID, String> {
    @Override
    public String convertToDatabaseColumn(UUID uuid) {
        return uuid.toString();
    }

    @Override
    public UUID convertToEntityAttribute(String dbData) {
        return UUID.fromString(dbData);
    }
}

Next, we can use the @Convert annotation on the UUID field in our JPA entity:

@Entity
public class UserWithConverter{
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Convert(converter = UUIDConverter.class)
    @Column(columnDefinition = "varchar")
    private UUID uuid;

    // getters and setters
}

The @Convert annotation configures JPA to automatically convert the UUID field to a string (VARCHAR) type when persisting the entity to the database, and vice versa when retrieving it:

Optional<UserWithConverter> userOptional = userWithConverterRepository.findByUuid(testId);
assertThat(userOptional.isPresent(), is(true));
assertThat(userOptional.get().getUuid(), equalTo(testId));

4. Conclusion

In this article, we discussed the PSQLException “operator does not exist: character varying = uuid” and how to fix it by explicitly casting the UUID value to a character varying or by using a custom converter in JPA.