sql - 错误 : CASE types character varying and numeric cannot be matched

Question

我在 Postgres 中写了一个 select 语句，其中包含如下 case 语句:

,(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

我收到以下错误。我尝试使用 IS 而不是 =，但没有用。如果没有这两个 case 语句，查询将完美运行。

ERROR: CASE types character varying and numeric cannot be matched SQL state: 42804

Answer 1

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 here -----------------------------------------------^