Postgres Delete Duplicate Rows

This query is to delete duplicate rows on multiple selected columns for Postgres.

DELETE FROM table_name 
WHERE id IN ( 
    SELECT id FROM ( 
        SELECT id, ROW_NUMBER() OVER w AS rnum 
        FROM table_name WINDOW w AS ( 
            PARTITION BY column_1, column_2, column_3, column_etc 
            ORDER BY id ) ) t WHERE t.rnum > 1);

Just plug in the table_name and columns you want to evaluate duplicates on. This query is the fastest out of several I’ve tried, evaluating and deleting 5 million duplicate rows on 8 columns in about 20 seconds.

Leave a Comment