Java 错误：运算符不存在：整数 = 字符变化，使用 Postgres 8.2

ERROR: operator does not exist: integer = character varying, using Postgres 8.2

提问by user2661243

I have a Java EE web application developed in an old version of Eclipse (Ganymede if I remember correctly). I recently migrated to Kubuntu 12.04 LTS and migrated the application to Eclipse Kepler (which I downloaded and installed from the Eclipse website). It is using Java Compliance Level 1.6 and the target container is Tomcat 6.

我有一个用旧版本的 Eclipse（如果我没记错的话，Ganymede）开发的 Java EE Web 应用程序。我最近迁移到 Kubuntu 12.04 LTS 并将应用程序迁移到 Eclipse Kepler（我从 Eclipse 网站下载并安装了它）。它使用 Java Compliance Level 1.6，目标容器是 Tomcat 6。

My problem is that I now receive the error:

我的问题是我现在收到错误：

org.postgresql.util.PSQLException: ERROR: operator does not exist: integer = character varying

org.postgresql.util.PSQLException：错误：运算符不存在：整数 = 字符变化

when the application encounters a page with a certain Postgres query in it. I'm using the JSTL sql:query and sql:param tags to implement a prepared statement within the jsp page. I know this is considered bad practice, but I'm not the original author and this technique is used throughout the application.

当应用程序遇到带有特定 Postgres 查询的页面时。我正在使用 JSTL sql:query 和 sql:param 标记在 jsp 页面中实现准备好的语句。我知道这被认为是不好的做法，但我不是原作者，并且在整个应用程序中都使用了这种技术。

The error occurs because of trying to assign a string to an integer in the sql:param tag. In the previous setup any casting happened transparently and there was no error. With the new setup I receive the error.

发生错误是因为尝试将字符串分配给 sql:param 标记中的整数。在之前的设置中，任何投射都是透明的，没有错误。使用新设置时，我收到错误消息。

I read that more strict type casting was introduced with Postgres 8.3 which would cause this error, but I am using the Postgres 8.2 JDBC 4 jar file within my application so it should work. I am stumped. Perhaps someone has an idea?

我读到 Postgres 8.3 引入了更严格的类型转换，这会导致此错误，但我在我的应用程序中使用 Postgres 8.2 JDBC 4 jar 文件，因此它应该可以工作。我难住了。也许有人有想法？

I came across a workaround, to multiply the string by 1 before making the comparison:

我遇到了一种解决方法，在进行比较之前将字符串乘以 1：

http://dev-answers.blogspot.co.uk/2010/08/type-coercion-in-jstl-for-sqlparam.html

http://dev-answers.blogspot.co.uk/2010/08/type-coercion-in-jstl-for-sqlparam.html

But this is a bit of a kludge and I would have many pages to modify. But it is academic because I should not be experiencing the problem anyway.

但这有点麻烦，我需要修改很多页面。但它是学术性的，因为无论如何我都不应该遇到这个问题。

Thanks for reading. Any help greatly appreciated.

谢谢阅读。非常感谢任何帮助。

采纳答案by Craig Ringer

You might be using the PostgreSQL 8.2 JDBC driver, but it looks like you're using a newer PostgreSQL serverversion. Try:

您可能使用的是 PostgreSQL 8.2 JDBC 驱动程序，但看起来您使用的是更新的 PostgreSQL服务器版本。尝试：

SELECT version()

Betcha it's 8.3 or newer.

Betcha 它是 8.3 或更高版本。

These queries aren't really right and should preferably just be fixed. If you must you can alter the system catalogs to allow the cast implicitly, but this should be a temporary workaround only, until you can fix the queries the application is sending. The workaround proposed in that blog post is horrible, but so is JSTL if it doesn't offer type casts in a heavily typed language. Personally I'd be more inclined to force an explicit coercion in the query, eg in the blog's example:

这些查询并不是真的正确，最好只是修复。如果必须，您可以更改系统目录以允许隐式转换，但这应该只是一个临时解决方法，直到您可以修复应用程序发送的查询。那篇博文中提出的解决方法很糟糕，但如果 JSTL 不提供重类型语言的类型转换，那么它也很糟糕。就我个人而言，我更倾向于在查询中强制进行显式强制，例如在博客的示例中：

<sql:query var="examples" dataSource="${exampleDataSource}">
    select ExampleName as "name"
    from ExampleTable 
    where ExampleId = ?::integer
    order by ExampleName ASC
    <sql:param value="${param['ID']}"/>
</sql:query>

? :: integeris a type castin the PostgreSQL shorthand syntax. You can write the SQL standard CAST(? AS integer)if you prefer.

? :: integer是PostgreSQL 速记语法中的类型转换。CAST(? AS integer)如果您愿意，您可以编写 SQL 标准。

See:

看：

• The PostgreSQL 8.3 release notes
• Peter's blog post

• PostgreSQL 8.3 发行说明
• 彼得的博文

The lesson here: Always read the release notesof major version upgrades before you upgrade.

这里的教训：在升级之前，请务必阅读主要版本升级的发行说明。

Heading

标题