Django / PostgreSQL: auto-increment a row's field on update

Question

I want to implement a row version number that increments whenever a table row is updated.

I'm using PostgreSQL.

Essentially I want this field to behave similarly to the following updated_at timestamp field: updated_at = models.DateTimeField(auto_now=True)

Except instead of updating with the current time, I want to auto-increment the current field value of the row. So if the row had version=1 and I made an update on the row, it would autoincrement that row to version=2.

I know I can implement this by overwriting the model save() method, but I was wondering if there was a way to implement this on the database level (and ideally through the Django ORM) so that it applies to any database accessing script/query.

You can write an update trigger in virtually every database. It doesn't involve Django. — thebjorn
– thebjorn, Commented Mar 9, 2018 at 21:50
are you aware that field like models.DateTimeField(auto_now=True) won't be updated when performing operation like YourModel.objects.filter().update(some_field=some_new_value) ? Is it ok for you? — andilabs
– andilabs, Commented Mar 9, 2018 at 22:51

andilabs · Accepted Answer · 2018-03-09 23:00:28Z

1

First of all are you aware that field like models.DateTimeField(auto_now=True) won't be updated when performing operation like YourModel.objects.filter().update(some_field=some_new_value) ? Is it ok for you?

1) If this is acceptable I will go for solution like that:

from django.db.models import F
from django.dispatch import receiver
from django.core.signals import post_save


@receiver(post_save, sender=YourModel)
def ensure_profile_exists(sender, instance, created, **kwargs):
    sender.objects.filter(pk=instance.pk).update(version=F('version')+1)

you can place that code inside your model definitions.

2) If you need to handle also .update() changes you should go for writing custom postgres trigger (see here docs https://www.postgresql.org/docs/10/static/plpgsql-trigger.html). Once created can be then registred in database using fake migration. See here: https://stackoverflow.com/a/31698995/953553

answered Mar 9, 2018 at 23:00

andilabs

23.5k14 gold badges126 silver badges159 bronze badges

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

2 Comments

Jad S Over a year ago

Awesome. I didn't know that about the update function. Definitely good to know. I think writing a migration with the SQL could be a winning solution. Do you know if this kind of trigger could also work on the SQLite DB I use in dev?

andilabs Over a year ago

you will need to distiniguish in migrations against what backend it shoudl be applied and choose the right trigger code for each

Collectives™ on Stack Overflow

Django / PostgreSQL: auto-increment a row's field on update

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related