How to reset the sequence for IDs on PostgreSQL tables?

As suggested by "Dmitry Shevchenko" you can run sqlsequencereset to solve your problem.

or

You can execute the SQL query generated by sqlsequencereset from within python in this way (using the default database):

from django.core.management.color import no_style
from django.db import connection

from myapps.models import MyModel1, MyModel2


sequence_sql = connection.ops.sequence_reset_sql(no_style(), [MyModel1, MyModel2])
with connection.cursor() as cursor:
    for sql in sequence_sql:
        cursor.execute(sql)

I tested this code with Python3.6, Django 2.0 and PostgreSQL 10.

Here's a short snippet to reset all sequences in Django 1.9+ (based on http://djangosnippets.org/snippets/2774/) and compatible with Python 3:

import os
from io import StringIO

os.environ['DJANGO_COLORS'] = 'nocolor'

from django.core.management import call_command
from django.apps import apps
from django.db import connection

commands = StringIO()
cursor = connection.cursor()

for app in apps.get_app_configs():
    label = app.label
    call_command('sqlsequencereset', label, stdout=commands)

cursor.execute(commands.getvalue())

So the quickest, easiest and most "Django" way to do this in my opinion is to use the following management command:

python manage.py sqlsequencereset app_name

After this, you'll get something such as:

BEGIN;
SELECT setval(pg_get_serial_sequence('"measurements_quantity"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "measurements.Quantities";
SELECT setval(pg_get_serial_sequence('"measurements.Prefixes"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "measurements.Prefixes";
COMMIT;

The next step is to run this in the python manage.py dbshell management command, so run this and then you'll see the interaction database shell in your terminal:

psql (11.7 (Debian 11.7-0+deb10u1), server 11.5 (Debian 11.5-1.pgdg90+1))
Type "help" for help.

postgres=# BEGIN;
BEGIN
postgres=# SELECT setval(pg_get_serial_sequence('"measurements.Quantities"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "measurements.Quantities";
 setval 
--------
      1
(1 row)

postgres=# SELECT setval(pg_get_serial_sequence('"measurements.Prefixes"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "measurements.Prefixes";
 setval 
--------
      1
(1 row)

postgres=# COMMIT;
COMMIT
postgres=# exit

Simple as that. The python manage.py sqlsequencereset app_name command will give you the SQL you need to run, and you run it in the dbshell.

No writing your own custom SQL or custom code and it will give you what you need in the correct format and db engine of choice.

PostgreSQL Command: ALTER SEQUENCE app_model_id_seq RESTART WITH 1

select setval('django_comments_id_seq', 12345);

This snippet Run sqlsequencereset on all apps reset all IDs of all Empty Models

Here is a more-or-less completely dynamic solution I just implemented in a management command that has no restriction as to the name of the Primary Key you are attempting to reset as it gathers it based on the connection params you have in settings.

The only sequencing I could not reset included PKs that are not integers, which is apparent in the PK for django.contrib.sessions, but again I have never run into sequencing errors with that so I doubt it is an issue.

Here is the command, run using python manage.py reset_sequences (obviously as long as your file/command is named reset_sequences.py)

import psycopg2
from django.conf import settings
from django.core.management.base import BaseCommand
from django.db import connections


def dictfetchall(cursor):
    """Return all rows from a cursor as a dict"""
    columns = [col[0] for col in cursor.description]
    return [
        dict(zip(columns, row))
        for row in cursor.fetchall()
    ]


class Command(BaseCommand):
    help = "Resets sequencing errors in Postgres which normally occur due to importing/restoring a DB"

    def handle(self, *args, **options):
        # loop over all databases in system to figure out the tables that need to be reset
        for name_to_use_for_connection, connection_settings in settings.DATABASES.items():
            db_name = connection_settings['NAME']
            host = connection_settings['HOST']
            user = connection_settings['USER']
            port = connection_settings['PORT']
            password = connection_settings['PASSWORD']

            # connect to this specific DB
            conn_str = f"host={host} port={port} user={user} password={password}"

            conn = psycopg2.connect(conn_str)
            conn.autocommit = True

            select_all_table_statement = f"""SELECT *
                                    FROM information_schema.tables
                                    WHERE table_schema = 'public'
                                    ORDER BY table_name;
                                """
            # just a visual representation of where we are
            print('-' * 20, db_name)
            try:
                not_reset_tables = list()
                # use the specific name for the DB
                with connections[name_to_use_for_connection].cursor() as cursor:
                    # using the current db as the cursor connection
                    cursor.execute(select_all_table_statement)
                    rows = dictfetchall(cursor)
                    # will loop over table names in the connected DB
                    for row in rows:
                        find_pk_statement = f"""
                            SELECT k.COLUMN_NAME
                            FROM information_schema.table_constraints t
                            LEFT JOIN information_schema.key_column_usage k
                            USING(constraint_name,table_schema,table_name)
                            WHERE t.constraint_type='PRIMARY KEY'
                                AND t.table_name='{row['table_name']}';
                        """
                        cursor.execute(find_pk_statement)
                        pk_column_names = dictfetchall(cursor)
                        for pk_dict in pk_column_names:
                            column_name = pk_dict['column_name']

                        # time to build the reset sequence command for each table
                        # taken from django: https://docs.djangoproject.com/en/3.0/ref/django-admin/#sqlsequencereset
                        # example: SELECT setval(pg_get_serial_sequence('"[TABLE]"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "[TABLE]";
                        try:
                            reset_statement = f"""SELECT setval(pg_get_serial_sequence('"{row['table_name']}"','{column_name}'), 
                                                    coalesce(max("{column_name}"), 1), max("{column_name}") IS NOT null) FROM "{row['table_name']}" """
                            cursor.execute(reset_statement)
                            return_values = dictfetchall(cursor)
                            # will be 1 row
                            for value in return_values:
                                print(f"Sequence reset to {value['setval']} for {row['table_name']}")
                        except Exception as ex:
                            # will only fail if PK is not an integer...
                            # currently in my system this is from django.contrib.sessions
                            not_reset_tables.append(f"{row['table_name']} not reset")

            except psycopg2.Error as ex:
                raise SystemExit(f'Error: {ex}')

            conn.close()
            print('-' * 5, ' ALL ERRORS ', '-' * 5)
            for item_statement in not_reset_tables:
                # shows which tables produced errors, so far I have only
                # seen this with PK's that are not integers because of the MAX() method
                print(item_statement)

            # just a visual representation of where we are
            print('-' * 20, db_name)

based on @Paolo Melchiorre I created a custom management command, which populates all the models from chosen apps.

from django.core.management.base import BaseCommand
from django.apps import apps
from django.core.management.color import no_style
from django.db import connection

class Command(BaseCommand):
    def handle(self, *args, **kwargs):
        self.stdout.write('Reset AutoFields ...')
        APPS = ['app1', 'app2']

        APPS = [apps.get_app_config(app) for app in APPS]
        models = []
        for app in APPS:
            models.extend(list(app.get_models()))

        sequence_sql = connection.ops.sequence_reset_sql(no_style(), models)
        with connection.cursor() as cursor:
            for sql in sequence_sql:
                self.stdout.write(sql)
                cursor.execute(sql)
        self.stdout.write(self.style.SUCCESS('Reset AutoField complete.'))

tested using python 3.7 and django 2.2.

Slightly OT, but if you want to first check if there are any sequences out of sync with their django tables, before running any potentially disruptive query, and your posgtres DB version is not too old, you could run the following query:

SELECT * FROM (
SELECT t.table_name AS table_name, (xpath('/row/max/text()', xmax))[1]::text::int AS table_max, t.seq_name, seq_max
FROM (
  SELECT table_name, data_type, query_to_xml(format('select max(%I) from %I.%I', column_name, table_schema, table_name), true, true, '') as xmax, pg_get_serial_sequence(table_name, 'id') AS seq_name
  FROM information_schema.columns
  WHERE column_name = 'id' AND table_schema = 'public'
) AS t
JOIN (
  SELECT schemaname || '.' || sequencename AS seq_name, last_value AS seq_max
  FROM pg_sequences
) AS s ON t.seq_name = s.seq_name
) as f
WHERE seq_max < table_max OR (seq_max IS NULL AND table_max IS NOT NULL)
ORDER BY table_name

(note that this will find any table with an id column and an associated out-of-sync sequence in the public schema, regardless of the fact that it is mapped onto a Django model)