Using column alias in a WHERE clause doesn't work

工作中需要写一个 SQL 进行查询，本来我是这样写的：

SELECT document.id, (COALESCE(CAST(document.internal_info->>'proofread_time' AS int), 0) - p500_end.end_utc) / 60 AS proofread_time
FROM document 
JOIN p500_end ON p500_end.doc_id = document.id 
WHERE document.created_utc > 1658592000 AND document.deleted = 0 AND proofread_time > 60 
ORDER BY id DESC

但是发现报这样的错误：

ERROR: column "proofread_time" does not exist

然后我把 WHERE 条件里的 proofread_time 替换成 (COALESCE(CAST(document.internal_info->>'proofread_time' AS int), 0) - p500_end.end_utc) / 60，就正常了。

咦，好奇怪，难道 alias 不能在 WHERE 条件里用吗，有点反直觉，于是我去查了下文档：

An output column’s name can be used to refer to the column’s value in ORDER BY and GROUP BY clauses, but not in the WHERE or HAVING clauses; there you must write out the expression instead.

原因就是 WHERE 语句和 HAVING 语句是在 column aliases 之前做的，所以没法引用，而 ORDER BY 和 GROUP BY 是在其之后，所以可以使用 aliased column

很古怪吧，反直觉！

但是把这么一长串的表达式写两遍真的很难受，所以我使用了 WITH 表达式来解决这个问题：

WITH results AS (
  SELECT document.id, (COALESCE(CAST(document.internal_info->>'proofread_time' as int), 0) - p500_end.end_utc) / 60 AS proofread_time
  FROM document 
  JOIN p500_end ON p500_end.doc_id = document.id 
  WHERE document.created_utc > 1658592000 AND document.deleted = 0 
  ORDER BY id DESC
)
SELECT id, proofread_time FROM results WHERE proofread_time > 60;

这样看起来就清晰多了。