PostgreSQL Featured

org.postgresql.util.PSQLException: ERROR: cannot cast type bytea to uuid

Yogesh Jadhav

Oct 10, 2022 • 2 min read

I was stuck solving this weird exception while working on a Spring Boot and Hibernate application. I verified all the configuration settings, double-checked the API calls and still, I was clueless as to what was actually causing the problem. The only conclusion that I had - The error occurs when there is a null value being sent a placeholder to the query.

Reference Code

Here is a sample Spring Data Repository code

@Query("select * from user where firstname=:name and id=:id", nativeQuery=true)
public List<User> findByNameAndId(@Param("name") String firstName, @Param("id")UUID id);

While I was executing this code, I was getting following error

org.postgresql.util.PSQLException: ERROR: cannot cast type bytea to uuid
Position: 512
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2505)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2241)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:310)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:447)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:368)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:158)
at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:108)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57)
at org.hibernate.loader.Loader.getResultSet(Loader.java:2292)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2050)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2012)
at org.hibernate.loader.Loader.doQuery(Loader.java:953)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:354)

What I tried?

Unfortunately, there was no definite solution available to fix this issue. I tried adding the CAST operator, ::UUID, etc. but nothing worked. Diving into Hibernate Source Code
After searching through various forums and blogs, I decided to dive into Hibernate source code. Looking for the clues as how Hibernate identifies parameter type and binds the values, I realized the error occurs in case of null values.

Hibernate tries to derive type of the parameter based on

the type of the value and locating the corresponding type in its mapping registry.
Use of org.hibernate.jpa.TypedParameterValue to hint hibernate about the parameter.

The second option is quite useful, in case the parameter value is occasionally null. And this was perfectly suitable for my scenario.

Solution

Now is the time to apply the actual Fix. The change was needed in the method definition of Spring Data Repository

@Query("select * from user where firstname=:name and id=:id", nativeQuery=true)
public List<user> findByNameAndId(@Param("name") String firstName, @Param("id")TypedParameterValue id);

and the calling code.

UUID userId = ... //Retrived from request parameter.
TypedParameterValue userIdParam = new TypedParameterValue(new PostgresUUIDType(), userId);
userRepository.findByNameAndId(userName, userIdParam);

And Voila!!! Everything worked like a charm. The code started working as expected.

The fix was pretty simple. Hibernate needed the type information and due to the null value, it was assuming the parameter is of Serializable type. With TypedParameterValue, Hibernate doesn't have to derive type using value.
The above solution works with calling procedures/functions and setParameter of NativeQuery instance as well. UUID is just an example in the above scenario, the approach works with all other types as well.