Postgres CASE WHEN IN query

Question

我在postgres 11中尝试了下面的查询

CASE
WHEN planning_status::varchar in (('Application Under Consideration'::varchar,'Appeal In Progress'::varchar))  then 'this_label'
WHEN planning_status::varchar  = 'Approved' and  actual_completion_date is not null then 'that_label'
ELSE 'reject_label'
END

我无法获得查询运行，最初在记录类型不匹配的操作符上获得错误。我也尝试了IN (VALUES())方法。下面的工作:

CASE
WHEN planning_status = 'Application Under Consideration'  then 'this_label'
WHEN planning_status = 'Appeal In Progress'  then 'this_label'
WHEN planning_status = 'Application Received'  then 'this_label'
WHEN planning_status  = 'Approved' and  actual_completion_date is not null then 'that_label'
ELSE 'reject_label'
END

是否可以在字符串的CASE WHEN查询中使用IN查询?字符串是分类的，但不是这样存储的

Answer 1

问题是双括号:

-- this doesn't work:
SELECT CASE WHEN 1 IN ((1, 2)) THEN 'works' ELSE 'weird' END;
ERROR:  operator does not exist: integer = record
LINE 1: SELECT CASE WHEN 1 IN ((1, 2)) THEN 'works' ELSE 'weird' END...
^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
-- this works:
SELECT CASE WHEN 1 IN (1, 2) THEN 'works' ELSE 'weird' END;
case  
═══════
works
(1 row)

原因是在第一个语句中，内括号形成了一个包含两个元素的复合类型(record)，而PostgreSQL不知道如何将其与整数1进行比较。

Answer 2

如果=版本工作，那么这个应该工作:

(CASE WHEN planning_status IN ('Application Under Consideration', 'Appeal In Progress', 'Application Received')
THEN 'this_label'
WHEN planning_status  = 'Approved' and  actual_completion_date is not null 
THEN 'that_label'
ELSE 'reject_label'
END)

不需要显式类型转换。如果这不起作用，planning_status的类型是什么?