How to delete duplicate rows without unique identifier

by

I like @erwin-brandstetter ‘s solution, but wanted to show a solution with the USING keyword:

DELETE   FROM table_with_dups T1
  USING       table_with_dups T2
WHERE  T1.ctid    < T2.ctid       -- delete the "older" ones
  AND  T1.name    = T2.name       -- list columns that define duplicates
  AND  T1.address = T2.address
  AND  T1.zipcode = T2.zipcode;

If you want to review the records before deleting them, then simply replace DELETE with SELECT * and USING with a comma ,, i.e.

SELECT * FROM table_with_dups T1
  ,           table_with_dups T2
WHERE  T1.ctid    < T2.ctid       -- select the "older" ones
  AND  T1.name    = T2.name       -- list columns that define duplicates
  AND  T1.address = T2.address
  AND  T1.zipcode = T2.zipcode;

Update: I tested some of the different solutions here for speed. If you don’t expect many duplicates, then this solution performs much better than the ones that have a NOT IN (...) clause as those generate a lot of rows in the subquery.

If you rewrite the query to use IN (...) then it performs similarly to the solution presented here, but the SQL code becomes much less concise.

Update 2: If you have NULL values in one of the key columns (which you really shouldn’t IMO), then you can use COALESCE() in the condition for that column, e.g.

  AND COALESCE(T1.col_with_nulls, '[NULL]') = COALESCE(T2.col_with_nulls, '[NULL]')

More Related Contents:

  1. Refactor a PL/pgSQL function to return the output of various SELECT queries
  2. How to check if a table exists in a given schema
  3. How to implement a many-to-many relationship in PostgreSQL?
  4. How to update selected rows with values from a CSV file in Postgres?
  5. Optimise PostgreSQL for fast testing
  6. Transpose latest rows per user to columns
  7. Best way to check for “empty or null value”
  8. Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?
  9. PostgreSQL parameterized Order By / Limit in table function
  10. How can I send email from PostgreSQL trigger?
  11. NOT NULL constraint over a set of columns
  12. Why do NULL values come first when ordering DESC in a PostgreSQL query?
  13. How do I alter the position of a column in a PostgreSQL database table?
  14. Query that ignore the spaces
  15. How to find a table having a specific column in postgresql
  16. Continuing a transaction after primary key violation error
  17. INSERT COMMAND :: ERROR: column “value” does not exist
  18. Use email address as primary key?
  19. Bulk/batch update/upsert in PostgreSQL
  20. SQL query to find record with ID not in another table
  21. Postgres Error: More than one row returned by a subquery used as an expression
  22. Grant privileges for a particular database in PostgreSQL
  23. Find duplicate rows with PostgreSQL
  24. Upsert with a transaction
  25. How to compare dates in datetime fields in Postgresql?
  26. PostgreSQL how to create a copy of a database or schema?
  27. Temporal database design, with a twist (live vs draft rows)
  28. PostgreSQL nested INSERTs / WITHs for foreign key insertions
  29. How to bulk insert only new rows in PostreSQL
  30. Postgres: convert single row to multiple rows (unpivot)

Leave a Comment