How to solve the PostgreSQL :: cast operator issue with JPA and Hibernate
Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Hypersistence Optimizer is that tool!
Introduction
While answering questions on the Hibernate forum, I stumbled on the following question regarding the PostgreSQL ::
cast operation used in a JPA and Hibernate entity query.
Since it’s a very interesting use case, I decided to turn the answer into a dedicated blog post.
Domain Model
Considering we have the following entity:
@Entity(name = "Post") @Table(name = "post") public class Post { @Id @GeneratedValue private Long id; private String title; @Column(name = "created_on") private LocalDateTime createdOn; //Getters and setters omitted for brevity }
As I explained in this article, Hibernate supports Java 1.8 Date/Time types since version 5.1. Now that they were included in JPA 2.2, every JPA provider should start supporting them.
Now, let’s persist the following three Post
entities:
Post part1 = new Post(); part1.setTitle("High-Performance Java Persistence, Part 1"); part1.setCreatedOn( LocalDateTime.now().with( TemporalAdjusters.previous(DayOfWeek.MONDAY) ) ); entityManager.persist(part1); Post part2 = new Post(); part2.setTitle("High-Performance Java Persistence, Part 2"); part2.setCreatedOn( LocalDateTime.now().with( TemporalAdjusters.previous(DayOfWeek.TUESDAY) ) ); entityManager.persist(part2); Post part3 = new Post(); part3.setTitle("High-Performance Java Persistence, Part 3"); part3.setCreatedOn( LocalDateTime.now().with( TemporalAdjusters.previous(DayOfWeek.THURSDAY) ) ); entityManager.persist(part3);
The first Post
is created on a Monday, the second one on a Tuesday, and the last one on a Thursday.
The problem
We would now like to run a query so that we get all Post
entities matching the same day of the week as the one we provide through a query parameter.
For this, we are using a native SQL query because it’s like a Magic Wand.
If you are running on PostgreSQL, you might want to use the date_part
function and cast the parameter using the :: type conversion operator.
List<Post> posts = entityManager.createNativeQuery( "SELECT * " + "FROM post " + "WHERE " + " date_part('dow', created_on) = " + " date_part('dow', :datetime::date)", Post.class) .setParameter("datetime", Timestamp.valueOf( LocalDateTime.now().with( TemporalAdjusters.next(DayOfWeek.MONDAY))) ) .getResultList();
However, when running the query above, Hibernate throws the following exception:
java.lang.IllegalArgumentException: Unknown parameter name : datetime at org.hibernate.query.internal.QueryParameterBindingsImpl.getBinding(QueryParameterBindingsImpl.java:208) at org.hibernate.query.internal.AbstractProducedQuery.setParameter(AbstractProducedQuery.java:486) at org.hibernate.query.internal.NativeQueryImpl.setParameter(NativeQueryImpl.java:586)
That’s because of the :: type cast operator conflicts with the JPA : named parameter syntax.
So, how can we fix it?
There are two ways we can fix this issue. We can either escape the :
character using a \
or use the CAST
function.
Escaping the : character
If we escape each of the :
character using \
, so that ::
becomes \:\:
, then everything will work just fine:
List<Post> posts = entityManager.createNativeQuery( "SELECT * " + "FROM post " + "WHERE " + " date_part('dow', created_on) = " + " date_part('dow', :datetime\\:\\:date)", Post.class) .setParameter("datetime", Timestamp.valueOf( LocalDateTime.now().with( TemporalAdjusters.next(DayOfWeek.MONDAY))) ) .getResultList(); assertEquals(1, posts.size()); assertEquals( "High-Performance Java Persistence, Part 1", posts.get(0).getTitle() );
Using the CAST function
Another option to fix this issue is by using the PostgreSQL CAST function.
List<Post> posts = entityManager.createNativeQuery( "SELECT * " + "FROM post " + "WHERE " + " date_part('dow', created_on) = " + " date_part('dow', cast(:datetime AS date))", Post.class) .setParameter("datetime", Timestamp.valueOf( LocalDateTime.now().with( TemporalAdjusters.next(DayOfWeek.MONDAY))) ) .getResultList(); assertEquals(1, posts.size()); assertEquals( "High-Performance Java Persistence, Part 1", posts.get(0).getTitle() );
And that works as expected.
Conversion from String
Alternatively, we could convert from a String
value as well. Consider that we get the query parameter as a String
:
DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern( "dd-MM-YYYY" ); String dateString = dateTimeFormatter.format( LocalDateTime.now().with(TemporalAdjusters.next(DayOfWeek.MONDAY)) );
This time, instead of using a simple CAST function call, we need to use the TO_TIMESTAMP function:
List<Post> posts = entityManager.createNativeQuery( "SELECT * " + "FROM post " + "WHERE " + " date_part('dow', created_on) = " + " date_part('dow', to_timestamp(:datetime, 'dd-MM-YYYY'))", Post.class) .setParameter("datetime", dateString) .getResultList(); assertEquals(1, posts.size()); assertEquals( "High-Performance Java Persistence, Part 1", posts.get(0).getTitle() );
And, it works like a charm.
If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.
Conclusion
Native SQL queries are very powerful, and that’s why you should use them. However, few database-specific operators, like the :: type conversion operator might interfere with the JPA-specific named parameter syntax.
Fixing this issue is actually very easy, as you can replace the operator in question with its equivalent SQL function call.

This helped me fix a major production problem.
Thank you
I’m glad I could help and stay tuned for more
Actually in your first example you want to remove the colon entirely in the cast(:datetime AS date) example, then it works splendidly.
Thanks. I’m glad you liked it.