CASE WHEN table1.text IS NULL THEN table2.numbers
ELSE table1.text
END AS newcolumn
When running this code I keep getting this error:
ERROR: CASE types character varying and integer cannot be matched
You would think that this would not cause problems since I’m creating a new column within my query. I’d also like to point out that I’m using an old version of PostgreSQL if that helps.
asked Mar 14, 2012 at 18:25
3
CASE WHEN table1.text IS NULL THEN table2.numbers::text ELSE table1.text END AS newcolumn
answered Mar 14, 2012 at 18:28
Clodoaldo NetoClodoaldo Neto
115k25 gold badges225 silver badges250 bronze badges
0
Problem is you are trying to add table1.text and table2.numbers into a single column. These two columns are two diff data types. try following
CASE WHEN table1.text IS NULL THEN CAST(table2.numbers AS VARCHAR(50)) ELSE table1.text END AS newcolumn
answered Mar 14, 2012 at 18:28
KafKaf
32.7k7 gold badges55 silver badges78 bronze badges
The data type represented by table2.numbers and table1.text has to be the same, so it looks like in this case you’ll need to CAST the value of table2.numbers
answered Mar 14, 2012 at 18:28
Brian DriscollBrian Driscoll
19.1k3 gold badges46 silver badges63 bronze badges
Try ISNULL
CASE WHEN ISNULL(table1.text) = 1 THEN table2.numbers ELSE table1.text END AS newcolumn
answered Mar 14, 2012 at 18:28
VinnieVinnie
3,8591 gold badge25 silver badges28 bronze badges
I think the error is pretty clear — the types of the two columns that may be used by the CASE statement aren’t compatible.
Why would you think that trying to use values from two columns of different types would not cause problems? You may be creating a new column in the result set, but it still has to have a type and that has to match all the potential values.
It may be possible in a lot of cases for it to infer the type, but that’s risky and may not necessarily be the choice that you want it to make, so it’s better for it to force you to make the decision. You’ll need to modify the type of one or the other columns in the CASE statement.
answered Mar 14, 2012 at 18:29
Anthony GristAnthony Grist
38.1k8 gold badges65 silver badges75 bronze badges
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
Mureinik
289k51 gold badges299 silver badges337 bronze badges
asked Jun 1, 2017 at 21:52
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 here -----------------------------------------------^
sainu
2,6763 gold badges23 silver badges41 bronze badges
answered Jun 1, 2017 at 21:57
MureinikMureinik
289k51 gold badges299 silver badges337 bronze badges
0
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;
answered Nov 1, 2019 at 13:08
Description of the problem:
Why does the CASE type character varying and numeric cannot be matched in the question appear?
In Postgresql, the concept of string mismatch does not appear until the operation of splicing strings.
I thought it would be no problem to use the concat function, but it is caused by the wrong place
The following is a glance at a piece of code:
EXPLAIN SELECT
se.enroll_number,
s.student_name,
CASE
WHEN pay.pay_method =’Pay College’ THEN
0
ELSE
pay.sum_pay
END AS sum_pay,
concat (
‘Paying universities’,
CASE
WHEN pay.pay_method !=’Paying colleges’ THEN
»
ELSE
» || pay.sum_pay
END
) AS remark,
pay.sum_deduct
FROM
(
SELECT
student_id,
pay_method,
SUM (pay_money) AS sum_pay,
SUM (channel_deduct) AS sum_deduct
FROM
payment_detail
WHERE
college_settlement_id = 28
GROUP BY
student_id,
pay_method
) AS pay
LEFT JOIN student AS s ON pay.student_id = s.student_id
LEFT JOIN student_enroll AS se ON pay.student_id = se.student_id
The concat method is used in the above code, because the concat method that was spliced on the else in the case when before, for the above reasons, I thought that the way of use was wrong, and I was surprised.
The above sql is a wrong demonstration. Under normal circumstances, it is not recommended to write this, because it affects the performance too much. You can use union all to query the table data in one step (with ideas for the above sql). Opinions, see how many ways to achieve?