Postgresql 案例和测试布尔字段
首先:我正在运行 postgresql 8.2 并在 pgAdmin 上测试我的查询。
我有一个包含一些字段的表,说:
mytable(
id integer,
mycheck boolean,
someText varchar(200));
现在,我想要一个与此类似的查询:
select id,
case when mycheck then (select name from tableA)
else (select name from tableB) end as mySpecialName,
someText;
我尝试运行并得到这个:
ERROR: CASE types character varying and boolean cannot be matched
SQL state: 42804
甚至试图欺骗 postgresql 也
case (mycheck::integer) when 0 then
不起作用。
所以,我的问题是:由于 sql 没有 if,只有 case,我应该如何使用布尔字段执行 if ?
First: I'm running postgresql 8.2 and testing my queries on 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
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?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
您的问题是您的值(
then
和else
之后的表达式)不匹配,而不是您的谓词(when
之后的表达式)不匹配。确保select name from tableA
和select name from tableB
返回相同的结果类型。mycheck
应该是一个布尔值。我在 PostgreSQL 9.0beta2 上运行了这个查询,并且(除了必须将
from mytable
添加到 SELECT 语句以及创建表tableA
和tableB
>),并且它没有产生任何类型错误。但是,当我运行以下命令时,我收到一条与您描述的错误消息非常相似的错误消息:上面的结果是:
Your problem is a mismatch in your values (expressions after
then
andelse
), not your predicate (expression afterwhen
). Make sure thatselect name from tableA
andselect name from tableB
return the same result type.mycheck
is supposed to be a boolean.I ran this query on PostgreSQL 9.0beta2, and (except for having to add
from mytable
to the SELECT statement as well as creating tablestableA
andtableB
), 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:The above yields:
我刚刚在 PostgreSQL 8 上运行得很好:
I just ran this fine on PostgreSQL 8: