"INT out of range" - How to Reset ID (Primary Key) in a Django Model

on
The "INT out of range" error in Django usually occurs when a field in your model has an integer type with a limited range (such as IntegerField) but you are trying to insert or update a value that exceeds the allowed range for that field.
 

 

How to Reset ID (Primary Key) in a Django Model

If you want to reset the id or primary key (pk) of a Django model, you'll need to consider how to handle database tables and how to reset auto-increment fields. Below are a few different methods:

Option 1: Reset ID using Database Command (PostgreSQL/MySQL)

The primary key in a model increments automatically. To reset it, you need to reset the auto-increment sequence of the table. Here are the commands for various databases:

PostgreSQL

ALTER SEQUENCE yourapp_modelname_id_seq RESTART WITH 1;

You can run this command via Django’s dbshell:

python manage.py dbshell

Then run the SQL command above, replacing yourapp_modelname_id_seq with the actual sequence name (usually composed of the app name, model name, and _id_seq).

MySQL

ALTER TABLE yourapp_modelname AUTO_INCREMENT = 1;

Option 2: Use SQL with cursor() in a Migration or Django Shell

You can also execute raw SQL through Django’s cursor():

from django.db import connection

with connection.cursor() as cursor:
    cursor.execute("ALTER SEQUENCE yourapp_modelname_id_seq RESTART WITH 1;")

Option 3: Truncate the Table

If you don’t need to preserve any of the existing records, you can truncate the table. This will delete all records and reset the primary key to 1:

from yourapp.models import ModelName

ModelName.objects.all().delete()

After deleting all records, you may need to reset the sequence manually as shown above. Alternatively, you could truncate the table with a raw SQL command:

from django.db import connection

with connection.cursor() as cursor:
    cursor.execute("TRUNCATE TABLE yourapp_modelname RESTART IDENTITY CASCADE;")

This will delete all rows and reset the ID sequence to 1.

Option 4: Custom Migration

If you're working in a development environment and want to do this through Django's migrations system, you can create a custom migration:

  1. Create an empty migration file:
    2. python manage.py makemigrations --empty yourappname
  2. Open the migration file and add the code to reset the sequence:
    3. from django.db import migrations, connection

def reset_model_id_sequence(apps, schema_editor):
    with connection.cursor() as cursor:
        cursor.execute("ALTER SEQUENCE yourapp_modelname_id_seq RESTART WITH 1;")

class Migration(migrations.Migration):

    dependencies = [
        ('yourappname', 'previous_migration_file'),
    ]

    operations = [
        migrations.RunPython(reset_model_id_sequence),
    ]

Caution

  • Production Environment: Be cautious when modifying primary keys in a production database. Changing primary key values can lead to loss of data integrity, especially if other tables have foreign key relationships.
  • Data Integrity: Ensure you don't have foreign key constraints that might be violated when resetting IDs.

Conclusion

There are different approaches you can use to reset the id primary key of a model, and the method you choose depends on your use case. The typical way involves either resetting the auto-increment sequence with an SQL command or truncating the entire table.

Fixing “integer out of range” in Django When the Primary Key Sequence Breaks

Context: This has been a recurring issue in our Cyclus app, so we’re hardening it with an automatic guard.

TL;DR

Postgres sequences can drift beyond the real MAX(id), causing django.db.utils.DataError: integer out of range on every insert. Align the sequence to MAX(id)+1, then add a small automation so it never blocks inserts again.

Symptoms

django.db.utils.DataError: integer out of range

In our case the table is public.cyclusemail_cyclus (Django model: Cyclus). The primary key is an INTEGER with a backing sequence public.cyclusemail_cyclus_id_seq. When that sequence jumped beyond the 32-bit limit, inserts started failing.

Diagnosis

Find the sequence and compare it to the current maximum id:

SELECT pg_get_serial_sequence('public.cyclusemail_cyclus','id');  -- => public.cyclusemail_cyclus_id_seq

SELECT MAX(id) AS max_id FROM public.cyclusemail_cyclus;
SELECT last_value, is_called FROM public.cyclusemail_cyclus_id_seq;

If the sequence value is ahead of MAX(id) (or worse, past 2,147,483,647), you’ve found the cause.

Immediate Fix (Reset the Sequence)

SELECT setval(
  'public.cyclusemail_cyclus_id_seq',
  COALESCE((SELECT MAX(id) FROM public.cyclusemail_cyclus), 0) + 1,
  false
);

After this, new inserts will work again.

Make It Robust (Automation for the Cyclus App)

Because this pops up periodically in Cyclus, we add a lightweight guard that auto-aligns the sequence at startup, in a daily cron, or in a health-check job.

Option A — Simple SQL guard (run daily)

-- Idempotent sequence alignment
SELECT setval(
  'public.cyclusemail_cyclus_id_seq',
  GREATEST(COALESCE((SELECT MAX(id) FROM public.cyclusemail_cyclus), 0) + 1, 1),
  false
);

Option B — Tiny Django management command

# manage.py command: python manage.py align_cyclus_sequence
from django.core.management.base import BaseCommand
from django.db import connection

class Command(BaseCommand):
    help = "Align PK sequence for cyclusemail_cyclus to MAX(id)+1"

    def handle(self, *args, **options):
        table = "public.cyclusemail_cyclus"
        seq   = "public.cyclusemail_cyclus_id_seq"
        with connection.cursor() as cur:
            cur.execute("SELECT COALESCE(MAX(id), 0) + 1 FROM public.cyclusemail_cyclus;")
            nextval = cur.fetchone()[0]
            cur.execute("SELECT setval(%s, %s, false);", [seq, nextval])
        self.stdout.write(self.style.SUCCESS("Sequence aligned: {} → {}".format(seq, nextval)))

Schedule this via cron or a deployment hook. This ensures the sequence is always in sync and prevents outages.

Future-Proofing

  1. Consider BIGINT for the PK if the table is long-lived/high-volume: 
    ALTER TABLE public.cyclusemail_cyclus
  ALTER COLUMN id TYPE bigint;
    Remember to migrate any foreign keys referencing this PK to bigint as well.
  2. Monitor sequences in your health checks by comparing last_value to MAX(id).

Takeaway

Sequence drift is a subtle but frequent operational issue in our Cyclus app. The quick reset fixes today’s incident; the guard and (optionally) moving to BIGINT make sure it doesn’t bite us again tomorrow. Keep sequences aligned, and inserts will stay healthy.

Comments

Post a Comment