postgresql PGError：错误：运算符不存在：布尔值~~*未知
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4742788/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PGError: ERROR: operator does not exist: boolean ~~* unknown
提问by Ulf Klose
Locally I'm developing with SQLite, but on my production host I'm running PostgreSQL. Locally everything's fine but not so on the production host.
我在本地使用 SQLite 进行开发，但在我的生产主机上我运行的是 PostgreSQL。在本地一切正常，但在生产主机上则不然。
I have built kind of a search form with which I can evaluate all data in my database with any combination I'd like. This seems to work fine as long as I don't use boolean and/or date fields. PostgreSQL doesn't seem to like my code very much…
我已经构建了一种搜索表单，我可以使用它以我想要的任何组合来评估我的数据库中的所有数据。只要我不使用布尔值和/或日期字段，这似乎就可以正常工作。PostgreSQL 似乎不太喜欢我的代码……
So, here's some example code:
所以，这里有一些示例代码：
unless params[:analysis][:sporty].blank?
tmp_conditions_customer << ["(sporty ILIKE ?)", "%#{params[:analysis][:sporty]}%"]
end
This evaluates to
这评估为
SELECT COUNT(*) FROM "customers" WHERE ((sporty ILIKE '%%')
Why's that anyway? Why the '%%'?
怎么会这样？为什么 '％％'？
For testing the deployment I'm using Heroku with the Exceptional plugin. This plugin gives me the following hint:
为了测试部署，我将 Heroku 与 Exceptional 插件一起使用。这个插件给了我以下提示：
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Thanks Exceptional, but what the heck does that mean? :-D Type casts for SQL queries? How's that gonna work?
谢谢 Exceptional，但这到底是什么意思？:-D SQL 查询的类型转换？那怎么办？
In my migration the database field looks like this:
在我的迁移中，数据库字段如下所示：
t.boolean :sporty
And in the form where I'm creating this data I'm using this code
在我创建此数据的表单中，我使用了此代码
<%= f.label :sporty %><br />
<%= f.select :sporty, options_for_select({ "Ja" => true, "Nein" => false }), { :include_blank => '-----'} %>
As I already mentioned, SQLite is my friend, seems to be the much stricter evaluation of PostgreSQL which causes the trouble.
正如我已经提到的，SQLite 是我的朋友，似乎是对 PostgreSQL 更严格的评估导致了麻烦。
Thanks for your help in advance.
提前感谢您的帮助。
采纳答案by Mike Sherrill 'Cat Recall'
Direct answer is near the bottom . . .
直接回答接近底部。. .
This evaluates to
这评估为
SELECT COUNT(*) FROM "customers" WHERE ((sporty ILIKE '%%')
Why's that anyway? Why the '%%'?
怎么会这样？为什么 '％％'？
In SQL, the '%' is a wildcard. But your problem seems to be that you're building a WHERE clause that has two opening parens, but only one closing paren.
在 SQL 中，'%' 是通配符。但是您的问题似乎是您正在构建一个 WHERE 子句，该子句有两个开头括号，但只有一个结尾括号。
A WHERE clause like this will probably return (or count) all rows:
像这样的 WHERE 子句可能会返回（或计算）所有行：
WHERE (sport ILIKE '%%')
Type casts for SQL queries? How's that gonna work?
SQL 查询的类型转换？那怎么办？
Standard SQL has a CAST() function. Skeleton syntax is
标准 SQL 有一个 CAST() 函数。骨架语法是
CAST (expression AS type)
So, for example, you can write
所以，例如，你可以写
CAST (<any timestamp> AS DATE)
to change a timestamp into a date data type, or
将时间戳更改为日期数据类型，或
CAST ('32' AS INTEGER)
to change the string '32' to the integer 32.
将字符串 '32' 更改为整数 32。
In my migration the database field looks like this:
在我的迁移中，数据库字段如下所示：
t.boolean :sporty
If the column "sporty" is Boolean, this is your real problem. If you try to use a string comparison on a Boolean (which you did: WHERE ((sporty ILIKE '%%') ) you'll get the error message you saw. You want the statement to read more like these:
如果“sporty”列是布尔值，这就是您真正的问题。如果您尝试在布尔值上使用字符串比较（您所做的： WHERE ((sporty ILIKE '%%') ），您将收到您看到的错误消息。您希望语句更像这样：
SELECT COUNT(*) FROM "customers" WHERE sporty;
SELECT COUNT(*) FROM "customers" WHERE sporty = true;
or
或者
SELECT COUNT(*) FROM "customers" WHERE NOT sporty;
SELECT COUNT(*) FROM "customers" WHERE sporty = false;
