Reset autoincrement sequences for IDs in Django application after all instances are deleted (PostgreSQL)
When managing a Django application, particularly during development or testing, developers often need to clear all data from a model. A common method is to use MyModel.objects.all().delete(). However, this presents a common frustration: the next record created does not start with an ID of 1. Instead, it continues from the last known value of the sequence.
This behavior occurs because executing a DELETE statement on a table does not reset the underlying autoincrement sequence generator in PostgreSQL. This article outlines the clean, idiomatic Django method for resolving this.
The problem: lingering sequences
Consider a model Product in a shop app. After deleting all records:
# This clears the table but not the sequence
Product.objects.all().delete()
# The new product will not have an ID of 1
new_product = Product.objects.create(name="Test Product")
print(new_product.id) # Might output 101, 500, or any other value
While you could execute raw SQL like TRUNCATE TABLE shop_product RESTART IDENTITY;, this approach is generally discouraged. It bypasses the Django ORM, is not database-agnostic, and can be cumbersome to run.
The idiomatic solution: sqlsequencereset
Django provides a built-in management command specifically for this task: sqlsequencereset.
This command is designed to generate the necessary SQL to reset the sequences for all models in a given application. It's important to note that this command only prints the SQL; it does not execute it.
Step-by-step implementation
To correctly reset your sequences, follow these steps.
1. Generate the SQL (Dry Run)
First, you can inspect the SQL that Django will generate. Run the command, passing in the name of your application:
xinit@localhost:~$ python manage.py sqlsequencereset shop
This will output the PostgreSQL-specific SQL, which typically looks something like this:
BEGIN;
SELECT setval(pg_get_serial_sequence('"shop_product"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "shop_product";
SELECT setval(pg_get_serial_sequence('"shop_category"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "shop_category";
-- ... (one line for each model in the 'shop' app)
COMMIT;
This command intelligently sets the sequence to the next available ID (1, if the table is empty, or max(id) + 1 if it is not).
2. Execute the SQL via dbshell
To execute the generated SQL against your database, you must pipe the output of sqlsequencereset directly into Django's dbshell management command. The dbshell command automatically uses the database credentials from your settings.py file.
This is the complete command to run in your terminal:
xinit@localhost:~$ python manage.py sqlsequencereset shop | python manage.py dbshell
Warning: The dbshell will execute the commands immediately. Ensure you are connected to the correct database (e.g., your development or staging database) before running this.
Conclusion
By using the sqlsequencereset | dbshell pipeline, you are leveraging Django's built-in tools to manage your database state cleanly. This method is safer than executing raw TRUNCATE commands, is easily repeatable, and ensures that after clearing a table, your new records will correctly begin with an ID of 1.
