How to Remove Duplicates in PostgreSQL
Last updated July 25, 2026 · By the SaturnSQL team
Delete with a self-join comparing ctid (the physical row id) to keep one row per group, or use a ROW_NUMBER() CTE when you want to control which duplicate survives. Add a unique index afterwards so duplicates cannot return.
ctid self-join (keeps the lowest ctid)
DELETE FROM customers a
USING customers b
WHERE a.email = b.email
AND a.ctid > b.ctid;ROW_NUMBER CTE (keep the newest row)
WITH ranked AS (
SELECT id,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) AS rn
FROM customers
)
DELETE FROM customers
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);Run the matching SELECT first, or wrap the DELETE in a transaction, to confirm you are removing the rows you expect.
Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free