Deleting duplicates in a postgres table
In order to solve the issue it is necessary to:
- Delete duplicates (and leaving only one entry, generally the most recent one).
- Add the natural key to the table
I’ve recently found a simple query that identifies the row to delete
SELECT id FROM (SELECT id, row_number() over (partition BY col_1, col_2, ... ORDER BY created_at DESC) AS rnum FROM quotes_Site.campaign_notes) t WHERE t.rnum > 1
In the example I’m returning the least recent ones, and the duplicates are identified by col1, col2, etc.
Combining that with a DELETE statement is straightforward.