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

最新推荐文章于 2024-10-04 07:22:19 发布

lk_db

最新推荐文章于 2024-10-04 07:22:19 发布

阅读量1k

点赞数

分类专栏： Highgo DB PostgreSQL

本文链接：https://blog.csdn.net/lk_db/article/details/78214903

版权

Highgo DB 同时被 2 个专栏收录

171 篇文章 2 订阅

订阅专栏

PostgreSQL

162 篇文章 2 订阅

订阅专栏

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)