Working postgresql query to native hibernate query PSQLException syntax error

Question

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?

fladdimir · Accepted Answer · 2022-02-07 18:46:59Z

3

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.

answered Feb 7, 2022 at 18:46

fladdimir

1,3651 gold badge8 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Brian Over a year ago

That did it, thanks a lot!

Collectives™ on Stack Overflow

Working postgresql query to native hibernate query PSQLException syntax error

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related