PostgreSQL：如何将分组行的列设为递增整数及引号用法解析

问题背景 #

我有一个名为issue的表，包含数千行数据，结构及数据如下：

需求是按照status字段分组，将每组内的listPosition更新为1、2、3……这样的递增整数，预期结果如下：

尝试过程与错误 #

第一次尝试的SQL #

UPDATE issue SET listPosition= 'dummy.listPosition'
FROM  
(
  SELECT id, row_number() over (partition by status order by id) AS "listPosition" FROM issue
) AS dummy
WHERE issue.id= 'dummy.id';

得到错误：

ERROR:  invalid input syntax for type integer: "dummy.id"
LINE 6: WHERE issue.id= 'dummy.id';

第二次尝试的SQL #

UPDATE issue SET listPosition= "dummy.listPosition"
FROM  
(
  SELECT id, row_number() over (partition by "dstOrgId", status order by id) AS "listPosition" FROM issue order by id
) AS dummy
WHERE issue.id = dummy.id;

WITH dummy AS
(
  SELECT id, row_number() over (partition by "dstOrgId", status order by id) AS "listPosition" FROM issue order by id
) 
UPDATE issue set listPosition = "dummy.listPosition"
FROM dummy
WHERE issue.id = dummy.id; 

均报错：

ERROR:  column "dummy.listPosition" does not exist
LINE 5: UPDATE issue set listPosition = "dummy.listPosition"

可行的SQL #

UPDATE issue SET "listPosition"= dummy."listPosition"
FROM  
(
  SELECT id, row_number() over (partition by "dstOrgId", status order by id) AS "listPosition" FROM issue order by id
) AS dummy
WHERE issue.id = dummy.id;

核心问题解答：PostgreSQL中单引号与双引号的意义及区别 #

• 单引号'：用于表示字符串常量，即固定的文本值。比如'dummy.id'会被PostgreSQL当作一个字符串字面量，而非引用dummy表中的id列，这就是第一次尝试报错的原因——你试图把字符串赋值给整数类型的id列，类型不匹配。
• 双引号"：用于引用标识符，包括表名、列名、别名等。当标识符包含特殊字符、大小写敏感或者与关键字重名时，必须用双引号包裹：
  • 若列名是listPosition（驼峰命名），PostgreSQL默认会将其转为小写listposition，因此必须用"listPosition"才能准确引用该列；
  • 引用表的列时，正确写法是dummy."listPosition"，而非"dummy.listPosition"——后者会被当作一个名为dummy.listPosition的整体列名，这就是第二次尝试报错的原因，PostgreSQL找不到这个不存在的列。
• 补充：如果标识符是全小写、不含特殊字符且不是关键字，可以不用双引号，PostgreSQL会自动识别；但只要涉及大小写混合或特殊字符，就必须用双引号保留原标识符的格式。

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