Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Deleting duplicates in postgres
-- A frequent question in IRC is how to delete rows that are duplicates over a set of columns, keeping only the one with the lowest ID.
-- This query does that for all rows of tablename having the same column1, column2, and column3.
DELETE FROM tablename
WHERE id IN (SELECT id
FROM (SELECT id,
row_number() over (partition BY column1, column2, column3 ORDER BY id) AS rnum
FROM tablename) t
WHERE t.rnum > 1);
-- Sometimes a timestamp field is used instead of an ID field.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment