如何用case语句和typecast更新列

Question

我尝试过以下操作，但它给出了错误

ERROR:  column "rating" is of type numeric but expression is of type text
LINE 2: set rating = CASE                     ^
HINT:  You will need to rewrite or cast the expression.

update rating  
set cast rating as varchar = CASE 
WHEN rating<0 THEN '0'  
When rating>3.5 THEN '4'  
When rating is null THEN '-1'    
End 

Answer 1

您是否尝试过将文本显式转换为数字？

update rating  
set cast rating as varchar = CASE 
WHEN rating<0 THEN '0'::numeric
When rating>3.5 THEN '4'::numeric
When rating is null THEN '-1'::numeric
End