Delete duplicate rows, keeping the oldest

Sql

Uses a window function to number the duplicates and removes everything but the first of each group.

DELETE FROM contacts
WHERE id IN (
  SELECT id FROM (
    SELECT id,
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM contacts
  ) ranked
  WHERE ranked.rn > 1
);

Download

Databases postgres duplicates cleanup chris

More in Databases

Find slow queries in Postgres Databases Ranks statements by total time spent. Needs the pg_stat_statements extension enabled. Add a column without locking the table Databases On Postgres 11 and later a nullable column with a default is a metadata-only change. Adding NOT NULL afterwards is the part that needs care. Group rows into a JSON array Databases Returns one row per author with their posts nested, instead of a join that repeats the author on every row. Which indexes is nobody using? Databases Lists indexes that have never been scanned, which are pure write cost. Check against a long uptime before dropping any. Upsert a row in SQLite Databases Inserts, or updates the existing row when the unique column already holds that value. Reset a Postgres sequence after a bulk import Databases Copying rows with explicit ids leaves the identity sequence behind, so the next insert collides with an existing row. Table sizes, largest first Databases Where the disk actually went, including indexes and TOAST.

Random picks

Narrow a union with a type guard TypeScript A predicate returning `x is T` teaches the compiler what a runtime check proved. A regular expression for ISO 8601 dates Text Processing Matches a date, optionally with a time and a zone. Deliberately strict about separators. Escape output in a PHP template PHP htmlspecialchars with the flags spelled out, because the defaults have changed between versions. Type a fetch response safely TypeScript unknown forces a parse step rather than letting an any spread silently through the codebase. Table-driven tests in Go Testing The idiomatic Go pattern: a slice of cases and a subtest per entry, each named so failures are readable. Walk a directory tree lazily Python pathlib keeps the paths as objects rather than strings, so joining and suffix checks stay readable. Make some properties optional TypeScript Composes the built-in helpers rather than writing a second interface that will drift from the first. Shell into a running container Containers Falls back to sh, since slim images often have no bash.