The Problem

Once a while people comes to me asking for help to remove duplicate rows on tables, and when I see how they're trying to accomplish this I get scared how complex is the queries to find the duplicates rows to remove.

So, in the beginning of my DBA carrer I found a simple, fast, no-locking way to do this. This approach should work in any Database Engine, not only Postgres. Let's dig into this.

The Scenario

Lets build our test scenario.

CREATE TABLE table_with_duplicate
( id serial PRIMARY KEY,
  code integer NOT NULL);

INSERT INTO table_with_duplicate
(code)
select * from generate_series (1, 1000)
union all
select * from generate_series (500, 2000)
union all
select * from generate_series (700, 1200)
union all
select * from generate_series (1600, 2500)

Note the utilization of "UNION ALL" clause instead of "UNION". The UNION clause does a aggregation at the end of the execution, we don't want that to this scenario. We need that exists duplicate rows for case study.

Analyzing

We have duplicate rows (or tuples, in PG World), some "code" appears 3 times. Let's see:

postgres=# select code, count(*) from table_with_duplicate group by 1 having count(*) > 1 limit 10;
 code | count 
------+-------
 1798 |     2
  652 |     2
  951 |     3
 1898 |     2
  758 |     3
 1750 |     2
 1136 |     2
  576 |     2
 1831 |     2
 1003 |     2
(10 rows)

And now? How to keep just one row?

Solving

The first thing you need to know is: Which row do you want to keep:

• The very first/older -- using min() function
• The last/newer -- using max() function

Here, we are going to keep the first one using the min() function:

postgres=# select code, count(*), min(id) as keep_id, array_agg(id) as id_all from table_with_duplicate group by 1 having count(*) > 1 limit 10;
 code | count | keep_id |   id_all    
------+-------+---------+------------
  500 |     2 |     500 | {1001,500}
  501 |     2 |     501 | {501,1002}
  502 |     2 |     502 | {502,1003}
  503 |     2 |     503 | {503,1004}
  504 |     2 |     504 | {504,1005}
  505 |     2 |     505 | {505,1006}
  506 |     2 |     506 | {1007,506}
  507 |     2 |     507 | {1008,507}
  508 |     2 |     508 | {1009,508}
  509 |     2 |     509 | {509,1010}
(10 rows)

What information do we have here?

• code = The "code" value that is duplicated
• count = The total number of tuples that is duplicated
• keep_id = The "id" that we want to maintain alive in the database
• id_all = All the "code" values that is duplicated. Note that the "keep" value is the lowest.

We are ready to build our DELETE:

postgres=# with cte_table as (
select
  a.code, count(*), min(a.id) as keep_id, array_agg(a.id) as all_id
from table_with_duplicate a
group by 1
having count(*) > 1
)
delete from table_with_duplicate b
using cte_table tab
where
  tab.code = b.code and
  tab.keep_id <> b.id;
DELETE 1403

Does we still have duplicated rows?

postgres=# select code, count(*), min(id), array_agg(id) as keep from table_with_duplicate group by 1 having count(*) > 1 limit 10;
 code | count | min | keep 
------+-------+-----+------
(0 rows)

postgres=# 

That's it. No more complex queries to find and remove duplicated rows.