Delete duplicate rows from small table

by

A faster solution is

DELETE FROM dups a USING (
      SELECT MIN(ctid) as ctid, key
        FROM dups 
        GROUP BY key HAVING COUNT(*) > 1
      ) b
      WHERE a.key = b.key 
      AND a.ctid <> b.ctid

More Related Contents:

  1. How to list table foreign keys
  2. Two SQL LEFT JOINS produce incorrect result
  3. Postgresql GROUP_CONCAT equivalent?
  4. How to update selected rows with values from a CSV file in Postgres?
  5. What is the maximum number of columns in a PostgreSQL select query
  6. Humanized or natural number sorting of mixed word-and-number strings
  7. Pass multiple values in single parameter
  8. Custom SERIAL / autoincrement per group of values
  9. How do I determine the last day of the previous month using PostgreSQL?
  10. PL/pgSQL checking if a row exists
  11. postgres column “X” does not exist
  12. Multiple CTE in single query
  13. Return pre-UPDATE column values using SQL only
  14. SQL syntax term for ‘WHERE (col1, col2) < (val1, val2)'
  15. PostgreSQL: encoding problems on Windows when using psql command line utility
  16. Get the distinct sum of a joined table column
  17. How much disk-space is needed to store a NULL value using postgresql DB?
  18. Aggregate a single column in query with many columns
  19. Aggregating (x,y) coordinate point clouds in PostgreSQL
  20. PostgreSQL convert columns to rows? Transpose?
  21. Does Postgres support nested or autonomous transactions?
  22. PostgreSQL delete with inner join
  23. Regular expression in PostgreSQL LIKE clause
  24. Why is iterating through a large Django QuerySet consuming massive amounts of memory?
  25. Update a column of a table with a column of another table in PostgreSQL
  26. Check if NULL exists in Postgres array
  27. Generate DEFAULT values in a CTE UPSERT using PostgreSQL 9.3
  28. PostgreSQL: Full Text Search – How to search partial words?
  29. Temporal database design, with a twist (live vs draft rows)
  30. PostgreSQL – dynamic value as table name [duplicate]

Leave a Comment