Find duplicate rows by one or more columns
List values that appear more than once with GROUP BY and HAVING, including how many times.
- SQL
- GreenSnippets
- MIT
- Executed with PostgreSQL 17
SELECT lower(email) AS email, COUNT(*) AS occurrences
FROM users
GROUP BY lower(email)
HAVING COUNT(*) > 1
ORDER BY occurrences DESC, email;Example / expected output
email | occurrences
---------------+-------------
ann@x.test | 3
bob@x.test | 2How it works
GROUP BY the columns that define a duplicate, then keep only groups with HAVING COUNT(*) > 1. Lowercase text columns first (lower(email)) if case differences should count as duplicates.
Once you have found duplicates, add a unique index so they cannot come back.
Related snippets
- SQL
Top N rows per group
Get the latest or highest N rows for each category using ROW_NUMBER in a subquery.
- SQL
Soft delete with a partial unique index
Keep deleted rows but still enforce unique emails among active rows using a partial unique index.
- SQL
Walk a tree with a recursive CTE
Query an employee/manager or category hierarchy to any depth using WITH RECURSIVE.
- SQL
Pivot rows into columns with FILTER
Turn row values into columns (for example monthly totals) using conditional aggregates, without crosstab.