PostgreSQL中WITH子句临时表无法用IN直接引用的问题

PostgreSQL里的IN操作符有严格的语法要求：它后面必须跟括号包裹的子查询，或者逗号分隔的常量值列表，不能直接引用CTE（公共表表达式）或表的名称。

两种写法的差异 #

• 能正常执行的WHERE test_id IN (SELECT id FROM TestsTodo)：括号里是一个完整的子查询，PostgreSQL会先执行这个子查询，从TestsTodo中取出所有id值组成集合，再用这个集合匹配test_id，完全符合语法规范。
• 触发错误的WHERE test_id IN TestsTodoIds：直接把CTE名称放在IN后面，PostgreSQL无法识别你要引用TestsTodoIds里的哪个字段，会把TestsTodoIds当成无效的语法标识符，所以抛出语法错误。

替代写法（更高效的方式） #

如果想用CTE来实现更新，也可以用JOIN的方式，性能有时候比IN子查询更好：

WITH TestsTodo AS (SELECT * FROM tests),
     TestsTodoIds AS (SELECT id FROM TestsTodo)
UPDATE test_results tr
SET your_column = new_value -- 替换成你的更新逻辑
FROM TestsTodoIds tti
WHERE tr.test_id = tti.id;

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