PostgreSQL删除重复数据技巧

1. 背景

在日常的开发过程中，数据库可能会出现这种重复数据的场景。 重复数据的来源可能是下面几种：

• 数据同步导致。
• 数据重复导入。
• 没有做好唯一性校验。
• 其他

比如下面这个例子：

CREATE TABLE meters (
    meter_id varchar(20),    -- 电表编号
    meter_name varchar(20)   -- 电表名称
);

-- 造一些包含重复记录的数据
INSERT INTO meters VALUES 
    ('M001', '徐家汇景园1号表'),
    ('M001', '徐家汇景园1号表'),  -- 重复两次
    
    ('M002', '陆家嘴花园2号表'),
    ('M002', '陆家嘴花园2号表'),
    ('M002', '陆家嘴花园2号表'),  -- 重复三次
    
    ('M003', '静安寺商厦3号表'),
    ('M003', '静安寺商厦3号表 '), -- 名称存在空格差异
    ('M003', '静安寺商厦3号表')，  
    
    ('M008', '上海中心大厦8号表'),
    ('M009', '环球金融中心9号表'),
    ('M010', '金茂大厦10号表');

查看下数据：

row_num | meter_id | meter_name        |
1       | M001     | 徐家汇景园1号表      |
2       | M001     | 徐家汇景园1号表      | 
3       | M002     | 陆家嘴花园2号表      |
4       | M002     | 陆家嘴花园2号表      |
5       | M002     | 陆家嘴花园2号表      |
6       | M003     | 静安寺商厦3号表      |
7       | M003     | 静安寺商厦3号表      |
8       | M003     | 静安寺商厦3号表      |
9       | M008     | 上海中心大厦8号表    | 
10      | M009     | 环球金融中心9号表    | 
11      | M010     | 金茂大厦10号表      |

理想状态下去重后的数据：

row_num | meter_id | meter_name            |
1       | M001     | 徐家汇景园1号表      | -- 保留一条
3       | M002     | 陆家嘴花园2号表      | -- 保留一条
6       | M003     | 静安寺商厦3号表      | -- 保留一条
9       | M008     | 上海中心大厦8号表    | -- 本身就是一条
10      | M009     | 环球金融中心9号表    | -- 本身就是一条
11      | M010     | 金茂大厦10号表      | -- 本身就是一条

2. 常规的删除方案

2.1 错误写法： 直接根据ID删除

DELETE FROM meters WHERE meter_id = 'M002';

这里会删除所有 M002 的记录，包括应该保留的那条。

2.2 错误写法：使用窗口函数

使用窗口函数过滤下重复的数据，然后把行号大于 1 的给删除掉。

DELETE
FROM meters
WHERE row_number() OVER (PARTITION BY meter_id ORDER BY meter_name) > 1;

PG 中不能在WHERE子句中直接使用窗口函数。

2.3 错误写法：使用自连接

DELETE FROM meters m1 
WHERE EXISTS (
    SELECT 1 FROM meters m2 
    WHERE m2.meter_id = m1.meter_id 
    AND m2.meter_name = m1.meter_name
);

这里会删除所有找到匹配的记录，这张表就会被清空，因为每条记录都能找到一个匹配项。

2. 4 错误写法：简单分组删除

DELETE FROM meters 
WHERE meter_id IN (
    SELECT meter_id
    FROM meters
    GROUP BY meter_id
    HAVING COUNT(*) > 1
);

上面这条 SQL 会删除所有重复 meter_id 的记录，而不是保留一条并删除其他重复的 。

执行后查询结果：

| meter_id | meter_name |
| M008     | 上海中心大厦8号表 |
| M009     | 环球金融中心9号表 |
| M010     | 金茂大厦10号表  |

可以看到留下的都是一条都没有重复的，跟预期的多条重复的保留一条不一样。

3 使用 PostgreSQL 的 ctid 删除重复数据

PostgreSQL 的 ctid 是一个隐藏的系统列 ， 可以用于标识行的物理位置:

• 格式为 ( block_number , tuple_index)。
• 每行数据都有唯一的 ctid 。
• 可用于精确定位要删除的重复记录。

比如上面的数据的 ctid 可能是这样：

ctid  | meter_id | meter_name          |
(0,1) | M001     | 徐家汇景园1号表      |
(0,2) | M001     | 徐家汇景园1号表      |
(0,3) | M002     | 陆家嘴花园2号表      |
(0,4) | M002     | 陆家嘴花园2号表      |
(0,5) | M002     | 陆家嘴花园2号表      |
(0,6) | M003     | 静安寺商厦3号表      |
(0,7) | M003     | 静安寺商厦3号表      |
(0,8) | M003     | 静安寺商厦3号表      |
(0,9) | M008     | 上海中心大厦8号表    | 
(0,10)| M009     | 环球金融中心9号表    | 
(0,11)| M010     | 金茂大厦10号表       |

使用 ctid 去删除重复数据：

DELETE
FROM meters
WHERE ctid IN (SELECT ctid
               FROM (SELECT ctid,
                            ROW_NUMBER() OVER (PARTITION BY meter_id ORDER BY ctid) as row_number
                     FROM meters) t
               WHERE row_number > 1);

这里稍微难点的就是内层的窗口函数查询。 PARTITION BY meter_id 就是按 meter_id 分组，相同的 meter_id 会被分到一组，类似 GROUP BY，但不会减少行数。

内层的查询结果如下：

ctid   | row_number  | meter_id
(0,1)  | 1          | M001 
(0,2)  | 2          | M001    

(0,3)  | 1          | M002 
(0,4)  | 2          | M002    
(0,5)  | 3          | M002    

(0,6)  | 1          | M003 
(0,7)  | 2          | M003    
(0,8)  | 3          | M003    

(0,9)  | 1          | M008 
(0,10) | 1          | M009 
(0,11) | 1          | M010 

然后通过 WHERE row_number > 1 过滤出除了第一条的重复数据 ctid ，这就为后续删除重复记录提供了依据。

最后去重的 6 条数据：

ctid   | meter_id
(0,1)  | M001     -- 保留最早的一条
(0,3)  | M002     -- 保留最早的一条
(0,6)  | M003     -- 保留最早的一条
(0,9)  | M008     -- 本身无重复
(0,10) | M009     -- 本身无重复
(0,11) | M010     -- 本身无重复

注意，尽量不要在生产环境中去执行这种语句，不建议在业务逻辑中去依赖 ctid 。 说到底还是要控制好数据的唯一性。