Delete Duplicate Rows SQL Generator
Duplicate rows creep in through retried imports, double-clicked forms and missing unique constraints. Removing them safely takes three steps: find which values repeat, decide which copy of each group to keep, and delete the rest without touching the survivors. This generator writes all of it for PostgreSQL and SQLite: a query that counts duplicate groups, a preview of the exact rows that will be deleted, a DELETE built on row_number() that keeps the first row by the order you choose (oldest, newest, or any column), and the unique index that stops the problem coming back. It handles case-insensitive keys such as emails, NULLs the same way a UNIQUE constraint does (or as equal values if you prefer), tables without a primary key (ctid in PostgreSQL, rowid in SQLite), and batched deletes for large tables. Every statement shape was run against test data on PostgreSQL 18.7 and SQLite 3.51 (512 combinations of options, with mixed-case, padded and NULL keys), and the deleted rows matched a reference implementation every time. Everything runs in your browser.
- Run the DELETE inside a transaction (BEGIN ... COMMIT) and compare the row count with the preview query before committing.
- lower(trim(col)) only works on text columns. Untick the case-insensitive box for numeric or date keys.
Related: could not create unique index (key is duplicated) and MySQL error 1062: Duplicate entry for key.
Do more than delete duplicate rows sql generator — meet Chat2DB
Chat2DB is an AI-powered SQL client for Windows, macOS and Linux. Write SQL in natural language, format and optimize queries automatically, and manage MySQL, PostgreSQL, Oracle and 20+ other databases in one workspace.
How to use
- Choose your database and enter the table and the columns that make two rows duplicates, ticking case-insensitive for text keys such as email.
- Set the keep rule (for example created_at ASC keeps the oldest row) and the primary key column, or leave it empty to use ctid or rowid.
- Run the count and preview queries first, then the DELETE inside a transaction, and finally create the unique index, in psql, sqlite3 or Chat2DB.
Frequently asked questions
How do I delete duplicate rows but keep one in SQL?
Number the rows inside each group of duplicates with row_number() OVER (PARTITION BY the duplicate columns ORDER BY your keep rule), then delete every row whose number is greater than 1. The row numbered 1 in each group is the one you keep, so ORDER BY created_at keeps the oldest copy and ORDER BY created_at DESC keeps the newest. Add the primary key as a final ORDER BY column so ties are broken the same way every time. The DELETE then removes those rows with WHERE id IN (subquery), and the same statement works in PostgreSQL and SQLite 3.25 or later.
How do I remove duplicates from a table that has no primary key?
PostgreSQL gives every row a ctid (its physical location) and ordinary SQLite tables have a hidden rowid, so the generator uses those when you leave the primary key field empty. They are stable within one statement, so the DELETE is safe, but ctid changes after UPDATE or VACUUM FULL, so never save ctid values for later. A SQLite table declared WITHOUT ROWID has no rowid, so enter its primary key instead.
Are rows with NULL values counted as duplicates?
By default the generator skips rows whose key contains a NULL, which matches how a UNIQUE constraint behaves: two NULL emails do not conflict. That also means the unique index you create afterwards will not complain about them. If you want NULLs treated as equal, tick the option: the window function groups NULLs together, and in PostgreSQL 15+ the index gets NULLS NOT DISTINCT. You can review the result in Chat2DB (https://chat2db.ai/download or https://app.chat2db.ai) before running the DELETE.
