PostgreSQL Library
Deleting ONLY the duplicates (leaving 1 row behind), with full control over batch sizes.
I've seen this question come up NUMEROUS times in various forums.
I've based this implementation on the work of others. (@Nikoay_S)
This is a GOOD LEARNING PIECE.
This seems straight forward enough.
Effectively, you can limit the number of rows you have coming back.
I have it set to 100 here.
The trick is HAVING count(1) > 1. This means each time you run, you will get at new set to work with.
Notice the way this runs, using the \WATCH command as a loop. (but it's an infinite loop. Version 16 of psql has iteration control!) [Also, this means it doesn't work in GUI tools]
Each iteration is it's own transaction. When you are done, the table is bloated (it should be vacuumed).
[I guess you could also use ROW_NUMBER OVER (PARTITION) rn ... WHERE rn > 1)]
— SETUP
drop table if exists big;
create temp table big ( other_val int );
insert into big select gs % 1000 + 1 from generate_series(1, 5000000) as gs;
create index on big (other_val);
vacuum analyze big;
DELETE from big
USING (select count(1) as cnt, other_val, min(ctid) as min_ctid from big group by other_val having count(1)>1 order by other_val desc limit 100) as dupes
where big.Other_val = dupes.other_val AND big.ctid > dupes.min_ctid;
\watch 0.5
3 · 1.4K ·