1. 云图网首页
  2. 技术专区
  3. 数据库

PostgreSQL: 如何删除单表重复数据？

• 数据库

今天在导入一张表数据时遇到重复数据问题，以前在维护 Oracle 时也遇到过， Oracle 库去重的方法很多，常用的是根据 rowid 进行去重，那么 PostgreSQL 库如何去除单表重复数据呢，可以通过 ctid 进行，下面是实验过程：

关于 ctid 的解释，可以参考之前的blog: https://postgres.fun/20101210100526.html

环境准备

创建测试表并插入初始数据，如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
mydb=> create table test_name (id integer,name varchar(32));  
CREATE TABLE
mydb=> insert into test_name values (1,'francs');  
INSERT 0 1  
mydb=> insert into test_name values (1,'francs');  
INSERT 0 1  
mydb=> insert into test_name values (1,'francs');  
INSERT 0 1  
mydb=> insert into test_name values (2,'fpZhou');  
INSERT 0 1  
mydb=> insert into test_name values (2,'fpZhou');  
INSERT 0 1  
mydb=> insert into test_name values (3,'A');  
INSERT 0 1  
mydb=> insert into test_name values (4,'B');  
INSERT 0 1  
mydb=> insert into test_name values (5,'C');  
INSERT 0 1

备注：实验场景，目标是去除 id 字段重复的数据。

查询初始化数据

1
2
3
4
5
6
7
8
9
10
11
12
 
mydb=> select * from test_name;  
id | name  
----+--------  
 1 | francs  
 1 | francs  
 1 | francs  
 2 | fpZhou  
 2 | fpZhou  
 3 | A  
 4 | B  
 5 | C  
(8 rows)

删除重复数据

查询重复数据情况

1
2
3
4
5
6
 
mydb=> select distinct id ,count(*) from test_name group by id having count(*) > 1;  
id | count  
----+-------  
 1 | 3  
 2 | 2  
(2 rows)

备注：上面查询出 id 为1 的记录有 3条， id 为 2的记录有两条。

查询重复的数据（即将删除的数据）

1
2
3
4
5
6
7
8
9
 
mydb=> select *  
       from test_name a  
       where a.ctid not in (select min(ctid) from test_name b group by id);  
id | name  
----+--------  
 1 | francs  
 1 | francs  
 2 | fpZhou  
(3 rows)

备注：上面查询列出重复的数据（即将删除的数据）。 

1
2
3
4
5
6
7
8
9
10
11
12
13
 
mydb=> select ctid,* from test_name where name='francs';  
ctid | id | name  
-------+----+--------  
(0,1) | 1 | francs  
(0,2) | 1 | francs  
(0,3) | 1 | francs  
(3 rows)

mydb=> select min(ctid) from test_name where name='francs';  
 min  
-------  
(0,1)  
(1 row)

删除重复的数据

1
2
3
 
mydb=> delete from test_name a  
mydb-> where a.ctid not in (select min(ctid) from test_name b group by id);  
DELETE 3

查询验证

1
2
3
4
5
6
7
8
9
 
mydb=> select * from test_name order by id;  
id | name  
----+--------  
 1 | francs  
 2 | fpZhou  
 3 | A  
 4 | B  
 5 | C  
(5 rows)

上述实验是通过表记录的 tid 来进行去重的，也可以根据业务需要，对表上的字段进行分析做为去重字段。

嵌套SQL删除重复数据

也可以使用以下SQL 删除重复数据

1
2
3
4
5
6
 
delete from test_name a  
where a.ctid <>  
(  
select max(b.ctid) from test_name b  
where a.id = b.id  
);

备注：在表数据量较大的情况下，这种删除方法效率很高。

原创文章，作者：carmelaweatherly，如若转载，请注明出处：https://blog.ytso.com/237820.html

赞 (0)
0
上一篇 2022年1月29日
下一篇 2022年1月29日

相关推荐

发表回复

登录后才能评论