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

Preserving Unique Instances in Duplicate Row Deletion

When working with large datasets, it is sometimes necessary to eliminate duplicate rows. However, in certain scenarios, it may be desirable to retain a single copy of each duplicate row. In such cases, a targeted approach is required to perform selective deletion.

Understanding the Problem

In PostgreSQL, the situation described involves deleting all but one instance of a set of duplicate rows. For example, if there are five records with the same values, the goal is to delete four of them, leaving one intact.

Finding a Solution

A comprehensive explanation of this issue is provided in the article "Removing duplicates from a PostgreSQL database." The authors address the specific challenge of dealing with vast amounts of data that cannot be grouped effectively.

A Simple Solution

The article recommends a straightforward solution:

DELETE FROM foo
WHERE id NOT IN (SELECT min(id) --or max(id)
                 FROM foo
                 GROUP BY hash)

In this query, "hash" represents the field or combination of fields that is being used to determine duplicates. By using either the minimum or maximum value of the "id" field for each duplicate group, one instance can be preserved.

This targeted approach allows for the efficient deletion of duplicate rows while maintaining a single copy for reference or analysis.

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