PostgreSQL带CTE的SQL查询报错：column "org_uid" does not exist 求助

你的查询问题出在主查询缺少了FROM cte语句，PostgreSQL无法识别你要从哪个数据集里选取org_uid等列，因此抛出该错误。

修正后的完整查询如下：

WITH cte AS (
  SELECT org_uid, uid, category, severity, source, created_at, 
  updated_at, delivery_date, finding_id, client_id, client_note, 
  definition, recommendations, details, resources, tags, text, type, sub_category, 
  (SELECT CAST(EXTRACT(EPOCH FROM MAX(fs.remediated_at) - findings.created_at) AS INTEGER) FROM "public"."findings-subjects" fs WHERE fs.finding_uid = findings.uid) AS time_to_remediate 
  FROM "public"."findings" 
  WHERE org_uid = '127cb14a-bbbb-dddd-aaaa-451e7cea3f94'
) SELECT org_uid, uid, category, severity, source, created_at, updated_at, delivery_date, 
finding_id, client_id, client_note, definition, recommendations, details, 
resources, tags, text, type, sub_category, time_to_remediate 
FROM cte
WHERE time_to_remediate BETWEEN 3 AND 7 
LIMIT 1000 
OFFSET 0

关键修正说明 #

在主查询的SELECT字段列表和WHERE条件之间添加了FROM cte，明确指定数据来源是你定义的CTE，这样所有列引用就能被正确解析。

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