在PostgreSQL上删除重复数据 [英] Delete Duplicate Data on PostgreSQL

问题描述

如何删除具有同类数据像这样的表的重复数据.我想将每个属性 id 的最新 updated_at 保留下来.

How to delete duplicate data on a table which have kind data like these. I want to keep it with the latest updated_at at each attribute id.

如下所示:

attribute id | created at          | product_id
1            | 2020-04-28 15:31:11 | 112235
4            | 2020-04-28 15:30:25 | 112235
1            | 2020-04-29 15:30:25 | 112236
4            | 2020-04-29 15:30:25 | 112236

推荐答案

您可以使用EXISTS条件.

You can use an EXISTS condition.

delete from the_table t1
where exists (select *
              from the_table t2
              where t2.created_at > t1.created_at
                and t2.attribute_id = t1.attribute_id);

这将删除存在相同attribute_id的另一行且其created_at值较大的所有行(因此，对于每个 attribute_id ，仅保留具有最高 created_at 的行).请注意，如果两个 created_at 值相同，则不会删除该 attribute_id

This will delete all rows where another row for the same attribute_id exists that has bigger created_at value (thus keeping only the row with the highest created_at for each attribute_id). Note that if two created_at values are identical, nothing will be deleted for that attribute_id

在线示例

这篇关于在PostgreSQL上删除重复数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！