Spring Boot Query annotation with nativeQuery doesn't work in Postgresql
Ok, I spend really long time trying to make this work for me, BUT I FINALLY GOT IT. Let the future adventurers and community know it as well.
What worked for me was casting to interval presented by A.H. here:
select current_timestamp + ( 2 || ' days')::interval;Your solution would be then something like that (when 2nd query is analyzed):
@Query(value = "SELECT n.* from nodes n WHERE n.node_id = 10510 AND n.last_good_ping > CURRENT_DATE - ( :interval )\\:\\:interval ", nativeQuery = true)Or for 3rd query (which is exactly how I made it work in my case)
@Query(value = "SELECT n.* from nodes n WHERE n.node_id = 10510 AND n.last_good_ping > CURRENT_DATE - ( ?1 )\\:\\:interval", nativeQuery = true)Please notice escaping :: becomes \\:\\:. In your pure SQL which you would test in your SQL editor use ::, but in your repository code within @Query these casting signs needs to be escaped (otherwise your Hibernate/JPA will be displeased).