☰

升级数据库后Postgresql报错

tag-icon • tag-icon postgresql
升级数据库后Postgresql报错

我们最近将 POSTGRESQL 升级到 9.4.4，现在当我的应用程序尝试运行查询时，我收到此错误。

日志：

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

我们目前拥有的版本：

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

您的 SQL 命令有一个拼写错误：... 'parentJobExecutionId' ROUP BY ...。它应该是GROUP BY。

（这特别难以看到，因为它需要水平滚动。将来，我建议打破长行以避免水平滚动条，\在每个添加的换行符的末尾使用并缩进以下行以表明它们应该是加入到上一行）。

发现这个错字后我就停止寻找了。可能有也可能没有其他人。仔细检查您的 SQL 命令。

相关内容