Delete duplicate rows from small table
A faster solution is
DELETE FROM dups a USING ( SELECT MIN(ctid) as ctid, key FROM dups GROUP BY key HAVING COUNT(*) > 1 ) b WHERE a.key = b.key AND a.ctid <> b.ctid
DELETE FROM dupes aWHERE a.ctid <> (SELECT min(b.ctid) FROM dupes b WHERE a.key = b.key);
This is fast and concise:
DELETE FROM dupes T1 USING dupes T2WHERE T1.ctid < T2.ctid -- delete the older versions AND T1.key = T2.key; -- add more columns if neededSee also my answer at How to delete duplicate rows without unique identifier which includes more information.