Django: How to delete duplicate rows based on 2 columns?

Question

I found out in this answer I can easily delete duplicate rows (duplication based on N columns) in a table with raw SQL.

Is there an equivalence using Django ORM ? The only stuff I found in Django concerned duplicated based on 1 column only.

Note : I know there is a way to prevent future duplicates (based on several fields) in Django, using unique_together field (but I didn't know before).

Thanks.

May you show your some of rows and columns of table?

Burger King
– Burger King

2015-09-21 08:05:53 +00:00
Commented Sep 21, 2015 at 8:05 — Burger King
– Burger King, Commented Sep 21, 2015 at 8:05

NeoWang · Accepted Answer · 2015-09-21 08:56:12Z

15

A direct translation from the SQL in the other answer into Django ORM:

from django.db.models import Min
# First select the min ids
min_id_objects = MyModel.objects.values('A', 'B').annotate(minid=Min('id'))
min_ids = [obj['minid'] for obj in min_id_objects]
# Now delete 
MyModel.objects.exclude(id__in=min_ids).delete()

This will results in 2 separate SQL queries instead of the one nested SQL provided in the other answer. But I think this is good enough.

answered Sep 21, 2015 at 8:56

NeoWang

18.8k24 gold badges87 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Keenan Over a year ago

If you have default ordering for your model, make sure to remove it by putting .order_by() at the end: min_id_objects = MyModel.objects.values('A', 'B').annotate(minid=Min('id')).order_by(). Otherwise, your default order field will appear in the GROUP BY clause of the query, which could mean you miss duplicates.

Andre Miras Over a year ago

@neowang It seems that this query could be a problem with a large MyModel dataset and just few dups, because the WHERE/IN clause created by the exclude can be huge.

Blairg23 Over a year ago

One caveat that I found is that if you want to delete the first object that was created and not the last object that was created, use max instead of min here.

Ivan · Accepted Answer · 2015-09-22 08:13:28Z

1

You can add an RunSQL operation with the SQL that removes duplicates in one of your migrations, before the operation that adds the uniqueness constraint.

A remark: if you are using sqlmigrate, RunSQL has an advantage of including its SQL into the resulting migration SQL.

edited Sep 22, 2015 at 8:13

answered Sep 21, 2015 at 8:07

Ivan

6,0432 gold badges32 silver badges48 bronze badges

2 Comments

David Dahan Over a year ago

I actually already added the uniqueness constraint (before deleting duplicates) and and applied migration.

Ivan Over a year ago

You can delete everything from the DB, delete migrations from the point where you added it, add the RunSQL and recreate the migrations (I am assuming it is not in production).

Collectives™ on Stack Overflow

Django: How to delete duplicate rows based on 2 columns?

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related