SQL 错误：CASE 类型的字符变化和数字无法匹配
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44317657/
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
ERROR: CASE types character varying and numeric cannot be matched
提问by Abhijit
I am writing a select statement in Postgres which contains case statement as follows:
我正在 Postgres 中编写一个包含 case 语句的 select 语句，如下所示：
,(case when all_loc.country = 'DE' then msc_si.buyer_id else msc_si.buyer_name end) as "purchasing_group_name_buyer_name" --story
,(case when all_loc.country = 'DE' then msc_si.planner_code else mscp.description end) as "mrp_controller_name" --story
I am getting the following error. I tried with ISinstead of =, didn't work. Without those two casestatements the query runs perfectly.
我收到以下错误。我尝试用IS而不是=，没有用。如果没有这两个case语句，查询将完美运行。
ERROR: CASE types character varying and numeric cannot be matched SQL state: 42804
错误：CASE 类型字符变化和数字无法匹配 SQL 状态：42804
回答by Mureinik
All the branches of a caseexpression should return the same datatype. One way to achieve that is to explicitly cast where needed:
case表达式的所有分支都应返回相同的数据类型。实现这一目标的一种方法是在需要的地方显式转换：
,(case when all_loc.country = 'DE' then msc_si.buyer_id::varchar else msc_si.buyer_name end) as "purchasing_group_name_buyer_name"
-- Here -----------------------------------------------^
,(case when all_loc.country = 'DE' then msc_si.planner_code::varchar else mscp.description end) as "mrp_controller_name"
-- And gere -----------------------------------------------^
回答by Biswajeet Praharaj
SELECT "table","schema",
CASE
WHEN "size" <= 1024 Then SIZE::varchar || 'MB'
WHEN "size" > 1024 AND "size" < 1000000 Then ("SIZE"/1024)::varchar || 'GB'
WHEN "size" > 1000000 THEN ("SIZE"/1024)/1024::varchar || 'TB'
END size
FROM SVV_TABLE_INFO
order by 1;
