Spring Boot JPA - SQL error on native query double apostrophe-CodePudding

I have a large query that works perfectly fine in normal postgres, but when I run in a JPA native query I get the following error:

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

The query contains the following line:

...
WHERE  year = extract( year FROM CURRENT_DATE - '2 month'::interval)::int 
    and month = extract( month FROM CURRENT_DATE - '2 month'::interval)::int 
...

I tried escaping by adding a \ before each apostrophe but the code won't compile:

error: illegal escape character
      "    WHERE  year = extract( year FROM CURRENT_DATE - '2 month'\:\:interval)\:\:int and month = extract( month FROM CURRENT_DATE - '2 month'\:\:interval)\:\:int  \n"

Is it not possible to use :: in a native query? Again this works perfectly fine when run normally.

CodePudding user response：

You can avoid the headache figuring the necessary escape sequence completely. Postgres uses the double colon (::) as a proprietary CAST operation, but the SQL standard is supported. Also, rather than casting the character string '2 month' as an interval you can declare it as such by prefixing it. Your clause then becomes:

where year =  cast (extract( year  FROM CURRENT_DATE - interval '2 month') as integer)
  and month = cast (extract( month FROM CURRENT_DATE - interval '2 month') as integer)

No escape required. And makes it a lot easier to read (i.e. understand).