How to remove duplicate rows in PostgreSQL |
글쓴이 : 모델광
날짜 : 2022-09-18 (일) 20:12
조회 : 6283
|
|
It is a rare event. Removing duplicate data from a table. But every now and then you may need to do a bit of spring cleaning. If you have a robust data model, every table should have a primary key which prevents the same data from being inserted. And when the table is designed badly the primary key cannot stop a duplicate row from being created. Here is an example of a bad data model I was recently confronted with. If you already know the data modeling
theory like the back of your hand, feel free to skip over to the next
paragraph.
In
the 상품이력 table rows with the same (이력일시, 상품번호, 상품명, 판매시작일자, 판매금액)
values can be inserted. The business key consisting of (이력일시, 상품번호)
columns shoud have been nominated as an identifier. The artificial
identifier (상품이력일련번호) here says nothing about the actual properties of
the data; in particular, the artificial identifier can not identify the row's uniqueness.
As such, the only uniqueness the artificail identifier can guarantee is
its own one. That is why duplicates can occur in the 상품이력 table; the
very thing normalization is trying to avoid. Artificial identifiers are
appropriate when there are many child entities associated with the
parent entity. The question of which identifier (the business key or the
surrogate key) to use is complicated - I am going to dive into some of
the complexity and inherent trade-offs in another note. When
duplicate rows are created, it is not easy to detect and delete them.
After googling for some ways of deleting duplicate rows, I found this
recent blog post: https://sqlfordevs.io/delete-duplicate-rows The poster introduced a pretty elegant way to erase duplicate rows using a window function to do it. In the example query he has a CTE where he is identifying all of the duplicates and every distinct row gets its own row number to show you how many duplicates there are. In this note we'll start with a quick recap of how to delete duplicate tuples introduced in the blog. Then look at an alternaive solution you can use in PostgreSQL database to wipe duplicate rows. The following test table T1 has been created using the system view pg_class: create TABLE t1 as select oid, relname, relnamespace from pg_class; Here is what the data in the table T1 looks like. select * from t1 limit 2; +-------+--------------+--------------+ | oid | relname | relnamespace | +-------+--------------+--------------+ | 16660 | emp | 58552 | | 2619 | pg_statistic | 11 | +-------+--------------+--------------+ I have inserted a row to make a duplicate row in the table T1. insert into t1 select 999,'pg_statistic',11; select * from t1 where relname='pg_statistic'; +------+--------------+--------------+ | oid | relname | relnamespace | +------+--------------+--------------+ | 2619 | pg_statistic | 11 | | 999 | pg_statistic | 11 | +------+--------------+--------------+ Note
that two rows have the same (relname, relnamespace) values. Let us say
that the (relname, relnamespace) value is the business key and you have
to remove duplicates. Here is an SQL statement Tobias Petry presented
followed by its execution plan: with w as ( select oid, row_number() over (partition by relname, relnamespace order by oid) as rownum from t1 ) delete from t1 using w where w.oid = t1.oid and w.rownum > 1; Delete on scott.t1 (actual time=1.040..1.042 rows=0 loops=1) In the above SQL statement the rows are partitioned by the columns indicating a duplicate row. Every partition is sorted by a number of columns to mark their importance. In the sample query the partition was sorted by the oid column in ascending order. The sorted records within the partition are assigned an incrementing number by the ROW_NUMBER window function. Any row can be deleted according to the desired number of remaining rows. When for example only one row should be kept, any row with a row number greater than one can be deleted. You can check whether a duplicate row was deleted by running the following query: select * from t1 where relname='pg_statistic'; +------+--------------+--------------+ | oid | relname | relnamespace | +------+--------------+--------------+ | 999 | pg_statistic | 11 | +------+--------------+--------------+ Likewise, if you want to keep the row with the highest oid, you use the order by oid desc clause instead of the order by oid clause. But this method presents a problem. The ROW_NUMBER() window function takes a lot of resources. So I propose another solution to speed up the process. You use the following DELETE statement with a subquery to remove rows whose values in the oid column are the highest ones. I have made a duplicate row again. insert into t1 select 2619,'pg_statistic',11; delete from t1 a where oid > (select min(b.oid) from t1 b where 1=1 and a.relname = b.relname and a.relnamespace = b.relnamespace); Delete on scott.t1 a (actual time=23.428..23.429 rows=0 loops=1) Even though the query is returning the expected result, the execution plan is extremely suboptimal. PostgreSQL had to run the subquery 661 times(loops=661). To be honest, as an Oracle guy for a long time I have used the above query. And Oracle produces an efficient plan. If you run the query in Oracle, the optimizer produces the following plan: -------------------------------------------------- Unlike PostgreSQL which access the T1 table 662 times(1+661), Oracle access the T1 table just two times (Id=3, Id=6). So I rewrote the query in PostgreSQL like the following: I have re-run the following query to make a duplicate row. insert into t1 select 2619,'pg_statistic',11;
DELETE Delete on scott.t1 a (actual time=0.342..0.343 rows=0 loops=1) As
you can see, compared to the query using the analytic function, there
is not really a lot of difference in terms of the number of block IOs
between the two - 25 and 19 respectively. But there is an important
difference which shows up when you look at the run time - 2.536 ms and
0.363 ms respectively. And compared to the query using a correlated
subquery, it is safe to say that you have to find a way to remove correlated subqueries in a delete statment.
PostgreSQL is very poor at dealing with correlated subqueries. Looking
at the execution plan closely, we can predict that with the same amount
of data PostgreSQL would be faster than Oracle because PostgreSQL does
not have to do the aggregation(group by) operation. Now you should know how to remove duplicate records in PostgreSQL.
Addendum Poor design leads to using a lot of storage. With regard to the sample ERD at the beginning of this note I can not see any reason why an artifical identifier is there. Disk space is cheap but not free. Database space used has some serious multipliers like the following: - Backups - Replicas - Development/Staging/Production Envronment - WAL files - Indexes - Shared buffer cache in memory |
How to remove duplicate rows in PostgreSQL > 나만의 강좌및 팁
 |
|||
 |
PostgreSQL 은 특별합니다.
단순한 데이터베이스가 아니라, 하나의 어플리케이션 플랫폼이기 때문입니다. |
||
 |
|
