错误：运算符不存在：integer =字符变化，使用Postgres 8.2 [英] ERROR: operator does not exist: integer = character varying, using Postgres 8.2
问题描述
我的问题是我现在收到错误：
org.postgresql.util.PSQLException：错误：运算符不存在：整数=字符变化
当应用程序遇到具有特定Postgres查询的页面在里面。我使用JSTL sql：query和sql：param标签来在jsp页面中实现一个准备好的语句。我知道这被认为是不好的做法，但是我不是原创作者，而是在整个应用程序中使用这种技术。
由于尝试分配一个字符串到sql：param标签中的整数。在以前的设置中，任何投射都是透明的，没有任何错误。使用新的设置，我收到错误。
我读到Postgres 8.3引入了更严格的类型转换，这将导致此错误，但是我使用Postgres 8.2 JDBC我的应用程序中的4个jar文件，所以它应该工作。我被困了可能有人有个想法？
在进行比较之前，我遇到了一个解决方法，将字符串乘以1：
http：// dev-answers.blogspot.co.uk/2010/08/type-coercion-in-jstl-for-sqlparam.html
但这是一个一个kludge，我会有很多页面来修改。但是这是学术性的，因为我不应该遇到问题。
感谢阅读。任何帮助非常感谢。
您可能正在使用PostgreSQL 8.2 JDBC驱动程序，但是看起来您正在使用较新的PostgreSQL 服务器版本。尝试：
SELECT version（）
Betcha是8.3或更高版本。
这些查询并不正确，最好只是修正。如果您必须修改系统目录以允许隐式转换，但这只能是临时解决方法，直到您可以修复应用程序发送的查询。该博客文章中提出的解决方法是可怕的，但JSTL如果不提供大量类型语言的类型转换，也是如此。个人来说，我更倾向于在查询中强制明确强制，例如在博客的例子中：
< sql： query var =examplesdataSource =$ {exampleDataSource}>
从ExampleTable
中选择ExampleName为name
其中ExampleId =？:: integer
按ExampleName排序ASC
< sql：param value =$ {param [ 'ID']}/>
< / sql：query>
？ :: integer 是PostgreSQL简写语法中的一种类型转换。如果您愿意，可以编写SQL标准 CAST（？AS整数）。
请参阅：
这里的教训：总是阅读主要的发行说明版本升级 。
标题
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.
My problem is that I now receive the error:
org.postgresql.util.PSQLException: ERROR: operator does not exist: integer = character varying
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.
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.
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?
I came across a workaround, to multiply the string by 1 before making the comparison:
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.
You might be using the PostgreSQL 8.2 JDBC driver, but it looks like you're using a newer PostgreSQL server version. Try:
SELECT version()
Betcha it's 8.3 or newer.
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:
<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>
? :: integer is a type cast in the PostgreSQL shorthand syntax. You can write the SQL standard CAST(? AS integer) if you prefer.
See:
The lesson here: Always read the release notes of major version upgrades before you upgrade.
Heading
这篇关于错误：运算符不存在：integer =字符变化，使用Postgres 8.2的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
