How Can I Delete Duplicate Rows in PostgreSQL While Preserving a Single Copy?

Preserving a Single Copy: Deleting Non-Identical Duplicate Rows

Maintaining data integrity often involves removing redundant records from databases. However, in certain scenarios, it may be necessary to retain one copy of each group of duplicate rows. This article addresses the challenge of deleting all but one row from a set of duplicates in PostgreSQL.

Solution: Selective Deletion Using Unique Identifiers

The query provided in the solution utilizes the concept of grouping and selecting the minimum (or maximum) value of a unique identifier field within each group of duplicates. By excluding rows with that minimum (or maximum) identifier from the deletion process, a single copy of each unique row can be preserved.

The following query demonstrates this approach:

DELETE FROM foo
WHERE id NOT IN (SELECT min(id) FROM foo GROUP BY hash);

In this query, the hash field represents a unique characteristic or combination of characteristics that defines the duplicate sets. By selecting the minimum id value from each group, the query identifies the row that will be retained and excludes the remaining duplicate rows from deletion.

The above is the detailed content of How Can I Delete Duplicate Rows in PostgreSQL While Preserving a Single Copy?. For more information, please follow other related articles on the PHP Chinese website!