阅读中文版本

Deduplicate Rows with Window Functions

Use row_number to keep the newest row per natural key and delete the rest without losing referential consistency.

SELECT id, key_col, row_number() OVER (PARTITION BY key_col ORDER BY updated_at DESC, id DESC) AS rn
FROM events;

DELETE FROM events e USING (
  SELECT id, row_number() OVER (PARTITION BY key_col ORDER BY updated_at DESC, id DESC) AS rn
  FROM events
) d WHERE e.id = d.id AND d.rn > 1;

What this recipe does

Remove duplicate rows by natural key while keeping a deterministic winner row.

Imports repeated a day of events, leaving duplicate business rows that break downstream counts.

Prerequisites

• PostgreSQL or a database with window functions
• A backup or transaction wrapper
• A clear natural key definition

Procedure

1. Rank duplicates and inspect survivors before deleting.

SELECT id, key_col, row_number() OVER (PARTITION BY key_col ORDER BY updated_at DESC, id DESC) AS rn
FROM events;

Expected result: Each natural key shows rn=1 for the row that should survive.

2. Delete all but the ranked winner in one statement.

DELETE FROM events e USING (
  SELECT id, row_number() OVER (PARTITION BY key_col ORDER BY updated_at DESC, id DESC) AS rn
  FROM events
) d WHERE e.id = d.id AND d.rn > 1;

Expected result: Only duplicate rows are removed; the newest per key remains.

How to confirm it worked

Run the commands above and confirm every expected result in order. If a step does not produce the stated output, use the troubleshooting notes before continuing. A passing run shows a repeated key query returns exactly one row per key and downstream counts match the expected total.

Troubleshooting

Wrong winner kept — Make the ORDER BY deterministic with a tiebreaker such as id, so results are stable.

Foreign keys block deletion — Repoint child rows to the winner before deleting, inside a transaction.

Limitations

• Window functions do not scale infinitely; batch large tables.
• Requires a reliable ordering column.
• Deletion is permanent; always back up first.