PostgreSQL 11中CASE语句枚举转字符串报错问题求助

报错原因 #

第一种CASE写法中，两个分支返回的都是字符串字面量（'ABANDONED'、'COMPLETED'），PostgreSQL会自动将整个CASE表达式的结果类型推导为text。但目标列state是自定义枚举类型PushState，PostgreSQL默认不会隐式将text类型转换为枚举类型，因此触发类型不匹配的报错。

第二种写法能正常执行的核心原因：

• 当else分支使用"state"时，该分支的类型是PushState，PostgreSQL会将整个CASE表达式的结果类型推导为PushState，此时THEN分支的字符串字面量会被隐式转换为对应的枚举值（只要字符串是枚举的有效值）。
• 多分支CASE写法中，else分支的"state"是枚举类型，同样让整个CASE表达式的类型变为PushState，前两个分支的字符串字面量会被隐式转换。加上WHERE条件限制了id范围，else分支实际不会执行，但类型推导仍会考虑所有分支的类型。

第一种写法的修复方案 #

只需显式将字符串字面量转换为PushState枚举类型，有两种常用方式：

方式一：使用类型转换操作符 :: #

UPDATE "Push"
SET "state" = CASE id WHEN 46 THEN 'ABANDONED'::PushState else 'COMPLETED'::PushState END
WHERE id in (46,47)

方式二：使用 CAST 函数 #

UPDATE "Push"
SET "state" = CASE id WHEN 46 THEN CAST('ABANDONED' AS PushState) else CAST('COMPLETED' AS PushState) END
WHERE id in (46,47)

这样CASE表达式的结果类型就与目标列state的类型匹配，能够正常执行更新操作。

内容的提问来源于stack exchange，提问作者Noitidart