Skip to content

Instantly share code, notes, and snippets.

@dennbagas

dennbagas/delete-duplicate-postgres.sql

Created January 20, 2021 05:13
Show Gist options
  • Save dennbagas/573ecd9473869519d37662697db6c460 to your computer and use it in GitHub Desktop.
Save dennbagas/573ecd9473869519d37662697db6c460 to your computer and use it in GitHub Desktop.
Download ZIP
Query to delete duplicate data by a column and keep the last one
Raw
delete-duplicate-postgres.sql
DELETE FROM user_devices WHERE ctid IN (
SELECT ctid FROM (
SELECT ctid, ROW_NUMBER() OVER (PARTITION BY device_id ORDER BY id DESC) AS rn
FROM user_devices
) t
WHERE rn > 1)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment