Postgresql 案例和测试布尔字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3096307/
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
Postgresql case and testing boolean fields
提问by Wracko
First: I'm running postgresql 8.2 and testing my queries on pgAdmin.
首先：我正在运行 postgresql 8.2 并在 pgAdmin 上测试我的查询。
I have a table with some fields, say:
我有一个包含一些字段的表，比如：
mytable(
id integer,
mycheck boolean,
someText varchar(200));
Now, I want a query similary to this:
现在，我想要一个与此类似的查询：
select id,
case when mycheck then (select name from tableA)
else (select name from tableB) end as mySpecialName,
someText;
I tried to run and get this:
我试图运行并得到这个：
ERROR: CASE types character varying and boolean cannot be matched
SQL state: 42804
And even trying to fool postgresql with
甚至试图欺骗 postgresql
case (mycheck::integer) when 0 then
didn't work.
没有用。
So, my question is: since sql doesn't have if, only case, how I'm suppose to do an if with a boolean field?
所以，我的问题是：由于 sql 没有 if，only case，我应该如何使用布尔字段执行 if？
回答by Joey Adams
Your problem is a mismatch in your values (expressions after thenand else), not your predicate (expression after when). Make sure that select name from tableAand select name from tableBreturn the same result type. mycheckis supposed to be a boolean.
您的问题是您的值（then和之后的表达式else）不匹配，而不是您的谓词（ 之后的表达式when）。确保select name from tableA并select name from tableB返回相同的结果类型。 mycheck应该是一个布尔值。
I ran this query on PostgreSQL 9.0beta2, and (except for having to add from mytableto the SELECT statement as well as creating tables tableAand tableB), and it didn't yield any type errors. However, I get an error message much like the one you described when I run the following:
我在 PostgreSQL 9.0beta2 上运行了这个查询，并且（除了必须添加from mytable到 SELECT 语句以及创建表tableA和tableB），它没有产生任何类型错误。但是，当我运行以下命令时，我收到一条与您描述的非常相似的错误消息：
select case when true
then 1
else 'hello'::text
end;
The above yields:
以上产生：
ERROR: CASE types text and integer cannot be matched
回答by Justin Ethier
I just ran this fine on PostgreSQL 8:
我刚刚在 PostgreSQL 8 上运行得很好：
select id,
case when mycheck = true then (...)
else (...),
someText;
