英文:
Postgres complaining to cast enum to a string only if used in else
问题
PostgreSQL 11使用时出现了问题。
当运行以下查询时：
UPDATE "Push"
SET "state" = CASE id WHEN 46 THEN 'ABANDONED' else 'COMPLETED' END
WHERE id in (46,47)
PostgreSQL 报错：
列 "state" 的类型为 "PushState"，但表达式的类型为文本
提示：您需要重写或转换表达式。
然而，如果从 else 中移除 'COMPLETED' 并放入 else "state"，它将在不报错的情况下运行：
UPDATE "Push"
SET "state" = CASE id WHEN 46 THEN 'ABANDONED' WHEN 47 THEN 'COMPLETED' else "state" END
WHERE id in (46,47)
我不明白为什么会出现这种情况。有人知道吗？理想情况下，我想使用第一个版本。
英文:
I am using Postgres 11.
When I run this query:
UPDATE "Push"
SET "state" = CASE id WHEN 46 THEN 'ABANDONED' else 'COMPLETED' END
WHERE id in (46,47)
Postgres complains with:
> column "state" is of type "PushState" but expression is of type text
HINT: You will need to rewrite or cast the expression.
However if I remove the 'COMPLETED' from the else and put in else "state" it works without the error.
UPDATE "Push"
SET "state" = CASE id WHEN 46 THEN 'ABANDONED' WHEN 47 THEN 'COMPLETED' else "state" END
WHERE id in (46,47)
I don't understand why it's doing this. Does anyone know? Ideally I would like to go with my first version.
答案1
得分: 2
create table s (id int, state PushState);
insert into s (values(46,'ABANDONED'::PushState),(47,'MISC'));
UPDATE s
SET state = CASE id WHEN 46 THEN 'ABANDONED'::pushstate else 'COMPLETED' END
WHERE id in (46,47);
因为枚举只能表示较小的文本值范围，无法将文本隐式转换为枚举。可以反过来做。
PostgreSQL 无法知道此文本也将符合枚举条件。因此，您需要明确转换。
英文:
create table s (id int,state PushState);
insert into s (values(46,'ABANDONED'::PushState),(47,'MISC'));
UPDATE s
SET state = CASE id WHEN 46 THEN 'ABANDONED'::pushstate else 'COMPLETED' END
WHERE id in (46,47);
because enum can only represent less text value range, you cannot implicitly cast from text to enum. it's doable other way around.
postgres cannot know this text will aslo match the enum criteria. So you need explicit cast.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论