How to Find Duplicate Rows in PostgreSQL
Last updated July 25, 2026 · By the SaturnSQL team
Group by the columns that should be unique and keep groups with HAVING COUNT(*) > 1. Join the result back to the table when you need to see the full duplicate rows.
The GROUP BY columns define what counts as a duplicate. Ordering by the count puts the worst cases first, which helps when you are triaging rather than fixing everything at once. NULLs group together here, so a nullable column reports all its NULL rows as one large duplicate set even though a unique index would allow them.
SELECT email, COUNT(*) AS occurrences
FROM customers
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY occurrences DESC;See the full rows
The join pulls back the complete rows so you can compare them and decide which to keep. A window function is the alternative worth knowing: COUNT(*) OVER (PARTITION BY email) in a subquery gets the same result in one pass, and adding ROW_NUMBER() over the same partition marks which copy to delete.
SELECT c.*
FROM customers c
JOIN (
SELECT email FROM customers GROUP BY email HAVING COUNT(*) > 1
) d ON d.email = c.email
ORDER BY c.email, c.id;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