pg中删除表中重复记录的SQL

原创于 2017-10-12 14:54:58 发布 · 1.1k 阅读

1 ·

CC 4.0 BY-SA版权

Highgo DB 同时被 2 个专栏收录

171 篇文章

订阅专栏

PostgreSQL

162 篇文章

订阅专栏

本文介绍了一种在数据库中高效删除重复记录的方法，并通过具体SQL语句演示了如何操作。相较于传统方式，该方法能有效提升处理大量数据时的效率。

highgo=# select * from test;
id | name
----+------
1 |
2 |
3 |
3 |
(4 rows)

highgo=# delete from test a where a.ctid <> (select min(b.ctid) from test b where a.id=b.id);
DELETE 1
highgo=# select * from test;
id | name
----+------
1 |
2 |
3 |
(3 rows)

上面的语句在表test的记录比较多时，效率会比较差，这时可以使用下面更高效的语句删除重复的记录：
highgo=# insert into test values (3);
INSERT 0 1
highgo=# select * from test;
id | name
----+------
1 |
2 |
3 |
3 |
(4 rows)

highgo=# delete from test where ctid = any(array(select ctid from (select row_number() over (partition by id),ctid from test) x where x.row_number > 1));
DELETE 1
highgo=# select * from test;
id | name
----+------
1 |
2 |
3 |
(3 rows)