sql - 在 PostgreSQL 查询中转义列名中的字符？

Question

我有一个正在使用的查询，它在 MySQL 中运行良好，但在 Postgres 上运行时遇到问题。我收到一个语法错误，我确信有一个简单的解决方案，但在这里找不到任何相关信息。

很明显，问题与第一种情况下的数字 1 有关。

SELECT *, 
(
 (case when 1stFarmers > 0 THEN 1 ELSE 0 END) +
  (case when OldMcDonald > 0 THEN 1 ELSE 0 END) +
   (case when NewMcDonald > 0 THEN 1 ELSE 0 END) 

) as num_fruits

FROM (SELECT
  fruit, sum(farmers),
  sum(case when farmer = '1stFarmers' then harvest_count else 0 end) AS 1stFarmers,
  sum(case when farmer = 'OldMcDonald' then harvest_count else 0 end) AS Old_McDonald,
  sum(case when farmer = 'NewMcDonald' then harvest_count else 0 end) AS New_McDonald
FROM fruits
GROUP BY
  farmer) ft

---

我返回的错误:

syntax error at or near "stFarmers"
  Position: 27
(
 (case when 1stFarmers > 0 THEN 1 ELSE 0 END) +
             ^
  (case when OldMcDonald > 0 THEN 1 ELSE 0 END) +

Answer 1

使用双引号 " 转义包含数字的标识符(在您的例子中是列名):

(case when "1stFarmers" > 0 THEN 1 ELSE 0 END) +