CODEWITHSUNDEEP
postgresqlduplicatessql-deletepostgresql-9.3
Deniel Lin
Deniel Lin

Reputation: 3

Delete Duplicate Data on PostgreSQL

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.

Like as follows:

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

Upvotes: 0

Views: 488

Answers (1)

user330315
user330315

Reputation:

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);

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

Online example

Upvotes: 2

Related Questions