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
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?
Colons need to be escaped, so
:: for JPA should become
\\:\\:, as explained here: https://stackoverflow.com/a/32076541
: indicates a named parameter)
In your case you may also replace the
:: cast by the
CAST (myData as timestamp) syntax.