PostgreSQL 9.3按每10行递增更新字段语法问题求助

问题背景 #

我是一名SQL Server开发人员，当前在PostgreSQL 9.3环境中开展项目，需要实现：在x_list表中，每10行递增更新field1字段值——前10行设为'0001'，接下来10行设为'0002'，以此类推（表共100行）。

原代码与错误信息 #

原PL/pgSQL代码 #

DECLARE 
    lp_cnt INTEGER := 1;
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM x_list WHERE field1 IS NULL) THEN

    UPDATE x_list
    SET field1 = RIGHT('0000' + lp_cnt::VARCHAR, 4)
    WHERE id IN (SELECT id FROM x_list WHERE field1 IS NULL LIMIT 10)
        
        lp_cnt := lp_cnt + 1
    END IF;
END $$;

执行错误 #

ERROR:  syntax error at or near "INTEGER"
LINE 2:  lp_cnt INTEGER := 1;
                ^
********** Error **********

ERROR: syntax error at or near "INTEGER"
SQL state: 42601
Character: 18

错误原因 #

1. 变量声明位置错误：PostgreSQL的DO块中，变量声明必须放在$$内部的BEGIN语句之前，不能写在DO $$外面。
2. 字符串拼接运算符错误：PostgreSQL中字符串拼接用||，而非SQL Server的+。
3. 语句缺少分号：lp_cnt := lp_cnt + 1末尾未加分号，违反PL/pgSQL语法规范。
4. 逻辑判断错误：原代码用IF NOT EXISTS，实际应该是当存在空值时执行更新，逻辑完全写反。

修正后的PL/pgSQL代码 #

DO $$
DECLARE 
    lp_cnt INTEGER := 1;
BEGIN
    -- 循环处理所有未赋值的行，每批10行
    WHILE EXISTS (SELECT 1 FROM x_list WHERE field1 IS NULL) LOOP
        UPDATE x_list
        SET field1 = RIGHT('0000' || lp_cnt::VARCHAR, 4)
        WHERE id IN (SELECT id FROM x_list WHERE field1 IS NULL LIMIT 10);
        
        lp_cnt := lp_cnt + 1;
    END LOOP;
END $$;

更优实现方案（单条SQL完成） #

PostgreSQL 9.3支持窗口函数，无需PL/pgSQL循环，单条SQL即可高效完成需求：

UPDATE x_list
SET field1 = TO_CHAR(CEIL(ROW_NUMBER() OVER (ORDER BY id) / 10.0), 'FM0000')
WHERE field1 IS NULL;

逻辑说明 #

• ROW_NUMBER() OVER (ORDER BY id)：按id排序生成连续行号，保证分组顺序稳定。
• CEIL(ROW_NUMBER() / 10.0)：将每10行划分为一组，得到批次编号（1到10）。
• TO_CHAR(..., 'FM0000')：将批次号格式化为4位带前导零的字符串，满足'0001'、'0002'的格式要求。

该方案仅需一次表扫描，性能远高于循环更新，更贴合PostgreSQL的特性。

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