1

We recently upgraded POSTGRESQL to 9.4.4 and now when my application tries to run a query I am getting this error.

LOGS:

2016-03-15 16:34:51 ERROR http-nio-8282-exec-2:FeedRestController:224 - org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [ SELECT ji.JOB_INSTANCE_ID, ji.JOB_NAME, ji.JOB_KEY, e.JOB_EXEC,  e.START_TIME, COALESCE(childJobs.END_TIME,e.END_TIME) as END_TIME, EXTRACT(EPOCH FROM (COALESCE(childJobs.END_TIME,COALESCE(e.END_TIME,NOW())) - e.START_Tas RUN_TIME, COALESCE(childJobs.STATUS,e.STATUS) as STATUS, COALESCE(childJobs.EXIT_CODE,e.EXIT_CODE) as EXIT_CODE, e.EXIT_MESSAGE, e.CREATE_TIME, e.LAST_UP.VERSION, e.JOB_CONFIGURATION_LOCATION, feed.STRING_VAL as FEED_NAME, 'FEED' as JOB_TYPE FROM BATCH_JOB_EXECUTION e INNER JOIN ( SELECT b.STRING_VAL , MAEXECUTION_ID) AS JOB_EXECUTION_ID FROM BATCH_JOB_EXECUTION a INNER JOIN BATCH_JOB_EXECUTION_PARAMS b on a.JOB_EXECUTION_ID = b.JOB_EXECUTION_ID AND b.KEY_NAME = 'feed' AND a.STATUS NOT IN('STOPPED','STARTING') AND a.EXITOT IN('STOPPED','NOOP') INNER JOIN BATCH_JOB_EXECUTION_PARAMS c on c.KEY_NAME='jobType' AND c.STRING_VAL = 'FEED' c.JOB_EXECUTION_ID = b.JOB_EXECUTION_ID GROUP BY b.STRING_VAL ) feed on feed.JOB_EXECUTION_ID = e.JOB_EXECUTION_ID inner join BATCH_JOCE ji on ji.JOB_INSTANCE_ID = e.JOB_INSTANCE_ID LEFT JOIN ( SELECT MAX(e.END_TIME) as END_TIME ,e.STATUS, e.EXIT_CODE, p.STRING_VAL as PAREXECUTION_ID FROM BATCH_JOB_EXECUTION e INNER JOIN BATCH_JOB_INSTANCE ji on ji.JOB_INSTANCE_ID = e.JOB_INSTANCE_ID INNER JOIN BATCH_JOB_EXECUTION_PARAMS p on p.JOB_EXECUTION_ID = e.JOB_EXECUTION_ID AND p.KEY_NAME = 'parentJobExecutionId' ROUP BY e.STATUS, e.EXIT_CODE, p.STRING_VAL ) childJobs on childJobs.PARENT_JOB_EXECUTION_ID = e.JOB_EXECUTION_ID ORDER BY e. JOB_EXECUTION_ID DESC ]; neeption is org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying = bigint
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Position: 1805

Version we currently have:

postgres@abc:~ $ psql postgres -c 'SELECT version();'
PostgreSQL 9.4.4 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.4.7    20120313 (Red Hat 4.4.7-4), 64-bit
(1 row)
1
  • upgraded from which version to to 9.4.4 ?
    – lese
    Mar 16, 2016 at 7:37

1 Answer 1

0

Your SQL command has a typo: ... 'parentJobExecutionId' ROUP BY .... It should be GROUP BY.

(this was particularly hard to see because it required horizontal scrolling. In future, i recommend breaking long lines to avoid horizontal scroll bars, use \ at the end of each added line-break and indent the following line to indicate that they are supposed to be joined to the previous line).

I stopped looking after spotting this typo. There may or may not be others. Check your SQL command carefully.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .