Postgresql error after upgrading database

Question

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)

Answer 1

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.