合 PostgreSQL删除重复数据的几种方法
👉 本文共约970个字，系统预计阅读时间或需4分钟。
Oracle 去重的方法很多，常用的是根据 rowid 进行去重，PostgreSQL 如何去除单表重复数据呢？可以通过 ctid 进行，下面是实验过程。
一般方法示例
一、创建测试表
1 2 3 4 5 | david=# create table emp ( david(# id int, david(# name varchar); CREATE TABLE david=# |
二、插入测试数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | david=# insert into emp values (1, 'david'); INSERT 0 1 david=# insert into emp values (1, 'david'); INSERT 0 1 david=# insert into emp values (1, 'david'); INSERT 0 1 david=# insert into emp values (2, 'sandy'); INSERT 0 1 david=# insert into emp values (2, 'sandy'); INSERT 0 1 david=# insert into emp values (3, 'renee'); INSERT 0 1 david=# insert into emp values (4, 'jack'); INSERT 0 1 david=# insert into emp values (5, 'rose'); INSERT 0 1 david=# |
三、查询初始化数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | david=# select ctid, * from emp; ctid | id | name -------+----+------- (0,1) | 1 | david (0,2) | 1 | david (0,3) | 1 | david (0,4) | 2 | sandy (0,5) | 2 | sandy (0,6) | 3 | renee (0,7) | 4 | jack (0,8) | 5 | rose (8 rows) david=# |
查询重复数据数：
1 2 3 4 5 6 7 8 | david=# select distinct id, count(*) from emp group by id having count(*) > 1; id | count ----+------- 1 | 3 2 | 2 (2 rows) david=# |
查询出 id 为1的记录有3条，id 为2的记录有2条。
四、查询要保留的数据
以 min(ctid) 或 max(ctid) 为准。
1 2 3 4 5 6 7 8 9 10 11 | david=# select ctid, * from emp where ctid in (select min(ctid) from emp group by id); ctid | id | name -------+----+------- (0,1) | 1 | david (0,4) | 2 | sandy (0,6) | 3 | renee (0,7) | 4 | jack (0,8) | 5 | rose (5 rows) david=# |
五、删除重复数据
1 2 3 | david=# delete from emp where ctid not in (select min(ctid) from emp group by id); DELETE 3 david=# |
六、查看最后结果
1 2 3 4 5 6 7 8 9 10 11 | david=# select ctid, * from emp; ctid | id | name -------+----+------- (0,1) | 1 | david (0,4) | 2 | sandy (0,6) | 3 | renee (0,7) | 4 | jack (0,8) | 5 | rose (5 rows) david=# |
说明：如果表中已经有标明唯一的序列主键值，可以把该值替换上述的ctid直接删除。
优化方法
首先创建一张基础表，并插入一定量的重复数据，数据量51万，重复数据1万。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | drop table deltest,deltest2,deltest3,deltest_bk; create table deltest(id int, name varchar(255)); create table deltest_bk (like deltest); create table deltest2 (like deltest); create table deltest3 (like deltest); insert into deltest select generate_series(1, 500000), 'ZhangSan'; insert into deltest select generate_series(1, 10000), 'ZhangSan'; insert into deltest_bk select * from deltest; insert into deltest2 select * from deltest_bk; insert into deltest3 select * from deltest_bk; checkpoint; \timing on |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
