postgresql如何找到表中重復數(shù)據(jù)的行并刪除
更新時間：2023年05月05日 16:49:18 作者：大妮喲
這篇文章主要介紹了postgresql如何找到表中重復數(shù)據(jù)的行并刪除問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教
postgresql找到表中重復數(shù)據(jù)的行并刪除
創(chuàng)建測試表并插入數(shù)據(jù)
create table aaa(id bigserial,col1 varchar(255));
insert into aaa values(1,'b'),(2,'a'),(3,'b'),(4,'c');
select * from aaa;
找到重復行并刪除
方法1：ctid表示數(shù)據(jù)行在它所處的表內(nèi)的物理位置，ctid由兩個數(shù)字組成，第一個數(shù)字表示物理塊號，第二個數(shù)字表示在物理塊中的行號。
select * from aaa where ctid not in(select max(ctid) from aaa group by col1);
刪除重復行
delete from aaa where ctid not in(select max(ctid) from aaa group by col1);
方法2：利用exists
找到重復行
select * from aaa t1 where exists (select 1 from aaa t2 where t1.col1=t2.col1 and t1.id<t2.id )----exists后的意思是同一列相等，但是自增id不相等且id小的那一個
刪除重復行
delete from aaa t1 where exists (select 1 from aaa t2 where t1.col1=t2.col1 and t1.id<t2.id )
postgresql常用的刪除重復數(shù)據(jù)方法
最高效方法
測試環(huán)境驗證，6600萬行大表，刪除2200萬重復數(shù)據(jù)僅需3分鐘
delete from deltest a where a.ctid = any(array (select ctid from (select row_number() over (partition by id), ctid from deltest) t where t.row_number > 1));
PG中三種刪除重復數(shù)據(jù)方法
首先創(chuàng)建一張基礎表，并插入一定量的重復數(shù)據(jù)。
create table deltest(id int, name varchar(255)); create table deltest_bk (like deltest); insert into deltest select generate_series(1, 10000), 'ZhangSan'; insert into deltest select generate_series(1, 10000), 'ZhangSan'; insert into deltest_bk select * from deltest;
1. 常規(guī)刪除方法
最容易想到的方法就是判斷數(shù)據(jù)是否重復，對于重復的數(shù)據(jù)只保留ctid最小（或最大）的數(shù)據(jù)，刪除其他的。
explain analyse delete from deltest a where a.ctid <> (select min(t.ctid) from deltest t where a.id=t.id); ------------------------------------------------------------------------------------------- ? ? Delete on deltest a ?(cost=0.00..195616.30 rows=1518 width=6) (actual time=67758.866..67758.866 rows=0 loops=1) ? ? ? ?-> ?Seq Scan on deltest a ?(cost=0.00..195616.30 rows=1518 width=6) (actual time=32896.517..67663.228 rows=10000 loops=1) ? ? ? ? ?Filter: (ctid <> (SubPlan 1)) ? ? ? ? ?Rows Removed by Filter: 10000 ? ? ? ? ?SubPlan 1 ? ? ? ? ? ?-> ?Aggregate ?(cost=128.10..128.10 rows=1 width=6) (actual time=3.374..3.374 rows=1 loops=20000) ? ? ? ? ? ? ? ? ?-> ?Seq Scan on deltest t ?(cost=0.00..128.07 rows=8 width=6) (actual time=0.831..3.344 rows=2 loops=20000) ? ? ? ? ? ? ? ? ? ? ? ?Filter: (a.id = id) ? ? ? ? ? ? ? ? ? ? ? ?Rows Removed by Filter: 19998 Total runtime: 67758.931 ms select count(*) from deltest; count ------- 10000
可以看到，id相同的數(shù)據(jù)，保留ctid最小的，其他的刪除。相當于把deltest表中的數(shù)據(jù)刪掉一半，耗時達到67s多。相當慢。
2. group by刪除方法
group by方法通過分組找到ctid最小的數(shù)據(jù)，然后刪除其他數(shù)據(jù)。
explain analyse delete from deltest a where a.ctid not in (select min(ctid) from deltest group by id); ------------------------------------------------------------------------------------------- ? ? Delete on deltest a ?(cost=131.89..2930.46 rows=763 width=6) (actual time=30942.496..30942.496 rows=0 loops=1) ? ? ? ?-> ?Seq Scan on deltest a ?(cost=131.89..2930.46 rows=763 width=6) (actual time=10186.296..30814.366 rows=10000 loops=1) ? ? ? ? ?Filter: (NOT (SubPlan 1)) ? ? ? ? ?Rows Removed by Filter: 10000 ? ? ? ? ?SubPlan 1 ? ? ? ? ? ?-> ?Materialize ?(cost=131.89..134.89 rows=200 width=10) (actual time=0.001..0.471 rows=7500 loops=20000) ? ? ? ? ? ? ? ? ?-> ?HashAggregate ?(cost=131.89..133.89 rows=200 width=10) (actual time=10.568..13.584 rows=10000 loops=1) ? ? ? ? ? ? ? ? ? ? ? ?-> ?Seq Scan on deltest ?(cost=0.00..124.26 rows=1526 width=10) (actual time=0.006..3.829 rows=20000 loops=1) ? ? ?Total runtime: 30942.819 ms select count(*) from deltest; count ------- 10000
可以看到同樣是刪除一半的數(shù)據(jù)，使用group by的方式，時間節(jié)省了一半。但仍含需要30s，下面試一下第三種刪除操作。
3. 高效刪除方法
explain analyze delete from deltest a where a.ctid = any(array (select ctid from (select row_number() over (partition by id), ctid from deltest) t where t.row_number > 1)); ----------------------------------------------------------------------------------------- ? ? Delete on deltest a ?(cost=250.74..270.84 rows=10 width=6) (actual time=98.363..98.363 rows=0 loops=1) ? ? InitPlan 1 (returns 0)?>SubqueryScanont(cost=204.95..250.73rows=509width=6)(actualtime=29.446..47.867rows=10000loops=1)Filter:(t.rownumber>1)RowsRemovedbyFilter:10000?>WindowAgg(cost=204.95..231.66rows=1526width=10)(actualtime=29.436..44.790rows=20000loops=1)?>Sort(cost=204.95..208.77rows=1526width=10)(actualtime=12.466..13.754rows=20000loops=1)SortKey:deltest.idSortMethod:quicksortMemory:1294kB?>SeqScanondeltest(cost=0.00..124.26rows=1526width=10)(actualtime=0.021..5.110rows=20000loops=1)?>TidScanondeltesta(cost=0.01..20.11rows=10width=6)(actualtime=82.983..88.751rows=10000loops=1)TIDCond:(ctid=ANY(0)?>SubqueryScanont(cost=204.95..250.73rows=509width=6)(actualtime=29.446..47.867rows=10000loops=1)Filter:(t.rownumber>1)RowsRemovedbyFilter:10000?>WindowAgg(cost=204.95..231.66rows=1526width=10)(actualtime=29.436..44.790rows=20000loops=1)?>Sort(cost=204.95..208.77rows=1526width=10)(actualtime=12.466..13.754rows=20000loops=1)SortKey:deltest.idSortMethod:quicksortMemory:1294kB?>SeqScanondeltest(cost=0.00..124.26rows=1526width=10)(actualtime=0.021..5.110rows=20000loops=1)?>TidScanondeltesta(cost=0.01..20.11rows=10width=6)(actualtime=82.983..88.751rows=10000loops=1)TIDCond:(ctid=ANY(0)) ? ? Total runtime: 98.912 ms select count(*) from deltest; count ------- 10000
可以看到，居然只要98ms
總結(jié)
以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。
相關文章
這篇文章主要介紹了修改postgresql存儲目錄的操作方式，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
PostgreSQL+Pgpool實現(xiàn)HA主備切換的操作
這篇文章主要介紹了PostgreSQL+Pgpool實現(xiàn)HA主備切換操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-12-12
PostgreSQL 是一個功能強大的開源關系型數(shù)據(jù)庫系統(tǒng),基于 C 語言實現(xiàn),采用 PostgreSQL 許可證,這是一種自由軟件許可證,允許用戶自由使用、修改和分發(fā)源代碼,所以本文將給大家介紹PostgreSQL15.x安裝的詳細教程,需要的朋友可以參考下2024-09-09
這篇文章主要介紹了postgresql 性能參數(shù)配置方式，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
這篇文章主要介紹了postgresql 索引之 hash的使用詳解，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-02-02
看標題貌似一天的收獲不小，但實際上是被一個問題搞的要死，啥問題？額，又是NTFS權限的問題。2009-08-08
使用python-slim鏡像遇到無法使用PostgreSQL的問題及解決方法
這篇文章主要介紹了使用python-slim鏡像遇到無法使用PostgreSQL的問題及解決方法,本文給大家介紹的非常詳細,感興趣的朋友跟隨小編一起看看吧2024-08-08
解析PostgreSQL中Oid和Relfilenode的映射問題
這篇文章主要介紹了PostgreSQL中Oid和Relfilenode的映射問題,本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2021-01-01
查看postgresql數(shù)據(jù)庫用戶系統(tǒng)權限、對象權限的方法
這篇文章主要介紹了查看postgresql數(shù)據(jù)庫用戶系統(tǒng)權限、對象權限的方法,本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2020-12-12
PostgreSQL的整型、浮點型、固定精度數(shù)值和序列等數(shù)字類型
PostgreSQL（簡稱PGSQL）是一種開源關系型數(shù)據(jù)庫管理系統(tǒng),廣泛應用于企業(yè)級應用,文章詳細介紹了PostgreSQL的數(shù)字類型,包括整型、浮點型、固定精度數(shù)值型和序列類型,強調(diào)了選擇合適的數(shù)字類型對于數(shù)據(jù)庫的存儲效率、查詢性能和數(shù)據(jù)準確性的重要性2024-09-09