PostgreSQL不创建新表删除重复行：存储过程异常排查

先说说你写的存储过程为啥不行：

• SELECT * INTO _temp_table 逻辑错误：你定义的_temp_table是字符串变量，但SELECT INTO是把查询结果赋值给变量，不是创建表。这行要么会因为查询返回多行报错，要么根本没创建出临时表，后续操作自然全部失效。
• 就算改成正确的临时表创建方式，先删全表再插入的方式风险极高——如果执行中途出问题，原表数据会丢失，而且锁表时间长，影响其他业务操作。

下面给两种不用临时表、直接删除重复行的方法，根据你的表结构选择：

方法1：表有主键/唯一标识列（比如自增id） #

如果表A有主键（比如id），用窗口函数标记重复行，只保留每组内的一行，删掉多余重复项：

WITH duplicates AS (
    SELECT id,
           ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY id) AS rn
    FROM A
)
DELETE FROM A
USING duplicates
WHERE A.id = duplicates.id AND duplicates.rn > 1;

PARTITION BY col1, col2按重复列分组，ORDER BY id保留每组里id最小的行，rn>1的就是需要删除的重复行。

方法2：表只有col1和col2两列（无主键） #

PostgreSQL中每个行都有唯一的ctid可以用来区分不同行，用它来标记重复行：

WITH duplicates AS (
    SELECT ctid,
           ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY ctid) AS rn
    FROM A
)
DELETE FROM A
USING duplicates
WHERE A.ctid = duplicates.ctid AND duplicates.rn > 1;

逻辑和方法1一致，只是用ctid代替主键来识别不同行。

如果你非要修改原来的存储过程（不推荐），正确的临时表写法如下，但依然不如上面的方法安全高效：

CREATE PROCEDURE test_procedure()
LANGUAGE plpgsql
AS $$
BEGIN
    -- 正确创建临时表
    CREATE TEMP TABLE TempTable AS 
    SELECT * FROM A GROUP BY col1, col2;
    
    -- 开启事务避免数据丢失（原代码无事务，风险极大）
    BEGIN
        DELETE FROM A;
        INSERT INTO A SELECT * FROM TempTable;
        COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
            RAISE;
    END;
    
    DROP TABLE TempTable;
END; $$

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