Deleting duplicates in postgres

gistfile1.sql

-- 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.