Skip to content

Instantly share code, notes, and snippets.

@wriglz

wriglz/delete_duplicate_rows_based_on_ctid.sql

Created January 22, 2020 16:59
Show Gist options
  • Save wriglz/da8dc43d7d33b92b53bb2aa4f4b7b4cb to your computer and use it in GitHub Desktop.
Save wriglz/da8dc43d7d33b92b53bb2aa4f4b7b4cb to your computer and use it in GitHub Desktop.
Download ZIP
Replace schema.table and duplicate_field.
Raw
delete_duplicate_rows_based_on_ctid.sql
DELETE
FROM schema.table a USING
(SELECT MIN(ctid) AS ctid,
duplicate_field
FROM schema.table
GROUP BY duplicate_field
HAVING COUNT(*) > 1) b
WHERE a.duplicate_field = b.duplicate_field
AND a.ctid <> b.ctid;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment