Working postgresql query to native hibernate query PSQLException syntax error-CodePudding

I have the below postgresql query which works fine if used directly on the db:

select memory_inmb from app where dt=(select dt from app where org='AAAA' 

AND to_timestamp(dt/1000) >= date_trunc('month', '2021-07-01 06:07:07 00'::timestamp) 

AND to_timestamp(dt/1000) < date_trunc('month', '2021-08-01 06:07:07 00'::timestamp) 

limit 1) AND org='AAAA';

In my repository I wrote it as a native query like this:

@Query(value="select a.memory_inmb from app a where a.dt=(select a.dt from app a where a.org='AAAA' AND to_timestamp(a.dt/1000) >= date_trunc('month', '2021-07-01 06:07:07 00'::timestamp) AND to_timestamp(a.dt/1000) < date_trunc('month', '2021-08-01 06:07:07 00'::timestamp) limit 1) AND a.org='AAAA'", nativeQuery = true)
    
List<BigInteger> findMemory();

In my implementation class for now I just call return this.appRepository.findMemory();

This gives me the following syntax exception:

org.postgresql.util.PSQLException: ERROR: syntax error at or near ":"
  Position: 148

How come I can run the query directly on the db just fine, but using it as native query doesn't work?

CodePudding user response：

Colons need to be escaped, so :: for JPA should become \\:\\:, as explained here: https://stackoverflow.com/a/32076541
(for JPA : indicates a named parameter)

In your case you may also replace the :: cast by the CAST (myData as timestamp) syntax.