ERROR: CASE types character varying and numeric cannot be matched

Question

I am writing a select statement in Postgres which contains case statement as follows:

,(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 IS instead of =, didn't work. Without those two case statements the query runs perfectly.

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

Answer 1

All the branches of a case expression should return the same datatype. One way to achieve that is to explicitly cast where needed:

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