PostgreSQL使用CASE WHEN条件查询时出现2020附近语法错误问题

postgresql error: syntax error at or near "2020"
LINE 2: ... THEN amount * split_exchange_rate ELSE 0 END) AS 2020Q4_rev...

• PostgreSQL的标识符（列名、表名等别名）语法规则要求：未用双引号包裹的标识符必须以字母、下划线开头，不能直接以数字作为首字符。你设置的列别名2020Q4_revenue、2021Q1_revenue等均以数字开头，且未添加双引号包裹，语法解析器会将开头的2020识别为数值常量，无法和后续字符拼接成合法的别名，因此触发语法错误。

两种修复方式均可：

方案1：给数字开头的别名加双引号 #

符合PostgreSQL标识符规则，保留原有别名命名：

SELECT object_id
  , SUM(CASE WHEN created::date >= '2020-10-01' AND created::date <= '2020-12-31' THEN amount * exchange_rate ELSE 0 END) AS "2020Q4_revenue"
  , SUM(CASE WHEN created::date >= '2021-01-01' AND created::date <= '2021-03-31' THEN amount * exchange_rate ELSE 0 END) AS "2021Q1_revenue"
  , SUM(CASE WHEN created::date >= '2021-04-01' AND created::date <= '2021-06-30' THEN amount * exchange_rate ELSE 0 END) AS "2021Q2_revenue"
  , SUM(CASE WHEN created::date >= '2021-07-01' AND created::date <= '2021-09-30' THEN amount * exchange_rate ELSE 0 END) AS "2021Q3_revenue"
FROM raw_data.purchase_revenue pr
GROUP BY 1

方案2：调整别名格式为字母/下划线开头 #

无需添加引号，写法更简洁，也兼容更多SQL方言：

SELECT object_id
  , SUM(CASE WHEN created::date >= '2020-10-01' AND created::date <= '2020-12-31' THEN amount * exchange_rate ELSE 0 END) AS rev_2020Q4
  , SUM(CASE WHEN created::date >= '2021-01-01' AND created::date <= '2021-03-31' THEN amount * exchange_rate ELSE 0 END) AS rev_2021Q1
  , SUM(CASE WHEN created::date >= '2021-04-01' AND created::date <= '2021-06-30' THEN amount * exchange_rate ELSE 0 END) AS rev_2021Q2
  , SUM(CASE WHEN created::date >= '2021-07-01' AND created::date <= '2021-09-30' THEN amount * exchange_rate ELSE 0 END) AS rev_2021Q3
FROM raw_data.purchase_revenue pr
GROUP BY 1

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