Skip to content

Instantly share code, notes, and snippets.

@nicerobot

nicerobot/delete_duplicates.sql

Forked from varunchitale/delete_duplicates.sql
Created April 21, 2019 20:32
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save nicerobot/2d79bb91bdeed6c403a786c6c64982b7 to your computer and use it in GitHub Desktop.
Save nicerobot/2d79bb91bdeed6c403a786c6c64982b7 to your computer and use it in GitHub Desktop.
Download ZIP
Efficiently delete duplicates rows from a table with a set of specific constraints.
Raw
delete_duplicates.sql
DELETE FROM <table> a USING (
SELECT MIN(ctid) as ctid, var1, var2
FROM <same_table> b
GROUP BY 2,3 HAVING COUNT(*) > 1
) b
WHERE a.var1 = b.var1
and a.var2 = b.var2
AND a.ctid <> b.ctid
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment