Auto Increment Field in Django/Python

You are going to have to implement that feature yourself, I doubt very much that a relational db will do that for you, and for good reason: it means updating a potentially large number of rows when one row is deleted.

Are you sure you need this? It could become expensive.

You're probably better off leaving the values in the table alone and using a query to generate the numbering. You can use window functions to do this if you're up to writing some SQL.

SELECT
   output_column,
   ...,
   row_number() over (
     order by
       order_column)
FROM
  TheTable;

Instead of deleting orders - you should create a field which is a boolean (call it whatever you like - for example, deleted) and set this field to 1 for "deleted" orders.

Messing with a serial field (which is what your auto-increment field is called in postgres) will lead to problems later; especially if you have foreign keys and relationships with tables.

Not only will it impact your database server's performance; it also will impact on your business as eventually you will have two orders floating around that have the same order number; even though you have "deleted" one from the database, the order number may already be referenced somewhere else - like in a receipt your printed for your customer.

You could try using signals post_save and post_delete to query the appropriate objects, sort them, and then look for missing numbers and reassign/save as necessary. This might be pretty heavy for a lot of data, but for only a few items that change rarely, it would be ok.

from django.db.models.signals import post_delete
from django.dispatch import receiver

def fix_order(sorted_objects):
    #ensures that the given objects have sequential order values from 1 upwards
    i = 1
    for item in sorted_objects
        if item.order != i:
            item.order = i
            item.save()
        i += 1

@receiver(post_delete, sender=YourSortedModel)
def update_order_post_delete(sender, kwargs):
    #get the sorted items you need
    sort_items = YourSortedModel.objects.filter(....).order_by('order')
    fix_order(sort_items)

I came across this looking for something else and wanted to point something out:

By storing the order in a field in the same table as your data, you lose data integrity, or if you index it things will get very complicated if you hit a conflict. In other words, it's very easy to have a bug (or something else) give you two 3's, a missing 4, and other weird things can happen. I inherited a project with a manual sort order that was critical to the application (there were other issues as well) and this was constantly an issue, with just 200-300 items.

The right way to handle a manual sort order is to have a separate table to manage it and sort with a join. This way your Order table will have exactly 10 entries with just it's PK (the order number) and a foreign key relationship to the ID of the items you want to sort. Deleted items just won't have a reference anymore.

You can continue to sort on delete similar to how you're doing it now, you'll just be updating the Order model's FK to list instead of iterating through and re-writing all your items. Much more efficient.

This will scale up to millions of manually sorted items easily. But rather than using auto-incremented ints, you would want to give each item a random order id in between the two items you want to place it between and keep plenty of space (few hundred thousand should do it) so you can arbitrarily re-sort them.

I see you mentioned that you've only got 10 rows here, but designing your architecture to scale well the first time, as a practice, will save you headaches down the road, and once you're in the habit of it, it won't really take you any more time.

Try to set the value with type sequence in postgres using pgadmin.