PostgreSQL中SELECT字段顺序引发语法错误的原因及解决方法

错误原因 #

timestamp是PostgreSQL的保留关键字，尽管你在CTE中将其定义为列别名，但后续SELECT子句中直接引用时，语法解析器会优先将它识别为关键字而非列名，从而触发语法冲突。调换字段顺序后能运行是解析器的偶然兼容行为，并非规范写法。

解决方法 #

两种可靠方式可让原代码正常运行：

1. 用双引号包裹关键字别名 #

通过双引号明确告知解析器这是列名，而非关键字：

%%sql
WITH temp AS
(
    SELECT  SUBSTRING(created_at, 1, 10) AS "timestamp", COUNT(DISTINCT CASE WHEN score >= 9 THEN id END) AS Good,
            COUNT(DISTINCT CASE WHEN score <= 6 THEN id END) AS Bad, ROUND((Good - Bad) * 100.0 / (Good + Bad), 2) AS nps
    FROM    kusdk.nps
    GROUP BY  "timestamp"
    ORDER BY  "timestamp"
)
SELECT "timestamp", nps
FROM   temp
LIMIT 10;

2. 更换非关键字别名 #

避免使用保留关键字作为列名，比如改用date_str：

%%sql
WITH temp AS
(
    SELECT  SUBSTRING(created_at, 1, 10) AS date_str, COUNT(DISTINCT CASE WHEN score >= 9 THEN id END) AS Good,
            COUNT(DISTINCT CASE WHEN score <= 6 THEN id END) AS Bad, ROUND((Good - Bad) * 100.0 / (Good + Bad), 2) AS nps
    FROM    kusdk.nps
    GROUP BY  date_str
    ORDER BY  date_str
)
SELECT date_str, nps
FROM   temp
LIMIT 10;

额外优化提示 #

• 提取日期部分可使用PostgreSQL更简洁高效的语法：created_at::date，直接返回YYYY-MM-DD格式，替代SUBSTRING更易读。
• 原查询中(Good - Bad) * 100 / (Good + Bad)会触发整数除法导致精度丢失，改为100.0可强制浮点除法，保证计算准确性。

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