How to Remove Duplicate Rows in Redshift
Last updated July 25, 2026 · By the SaturnSQL team
Redshift has no ctid, so you cannot delete duplicates in place the Postgres way. Use ROW_NUMBER() in a CTE to keep one row per key, write the result to a new table, then swap it in for the original.
CREATE TABLE orders_dedup
DISTKEY (customer_id)
SORTKEY (created_at)
AS
WITH ranked AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY id
ORDER BY created_at DESC
) AS rn
FROM orders
)
SELECT id, customer_id, amount, status, created_at
FROM ranked
WHERE rn = 1;Swap the tables
BEGIN;
ALTER TABLE orders RENAME TO orders_with_dupes;
ALTER TABLE orders_dedup RENAME TO orders;
COMMIT;
DROP TABLE orders_with_dupes;Duplicates are common in Redshift because unique constraints are not enforced. Prevent them at load time with the staging-table upsert pattern rather than deduplicating after the fact.
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