Reset all sequences so that they continue after max(id) + 1? It looks like I messed up a database migration and while all sequences are there, they would start at 1, which triggers errors like:


  django.db.utils.IntegrityError: duplicate key value violates unique
  constraint "django_admin_log_pkey" DETAIL:  Key (id)=(2) already
  exists.


Is there a query/script I could run that would run across all tables in the database, look at any columns tied to a sequence and reset those sequences to max(column) + 1?

Using PostgreSQL v11.
I see you are using Django.
I implemented a command to resolve these issues for all databases in your settings file.
import psycopg2
from django.conf import settings
from django.core.management.base import BaseCommand
from django.db import connections


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


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

    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():
            table_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&quot;host={host} port={port} user={user} password={password}&quot;

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

            select_all_table_statement = f&quot;&quot;&quot;SELECT *
                                    FROM information_schema.tables
                                    WHERE table_schema = 'public'
                                    ORDER BY table_name;
                                &quot;&quot;&quot;
            # just a visual representation of where we are
            print('-' * 20, table_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&quot;&quot;&quot;
                            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']}';
                        &quot;&quot;&quot;
                        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('&quot;[TABLE]&quot;','id'), coalesce(max(&quot;id&quot;), 1), max(&quot;id&quot;) IS NOT null) FROM &quot;[TABLE]&quot;;
                        try:
                            reset_statement = f&quot;&quot;&quot;SELECT setval(pg_get_serial_sequence('&quot;{row['table_name']}&quot;','{column_name}'), 
                                                    coalesce(max(&quot;{column_name}&quot;), 1), max(&quot;{column_name}&quot;) IS NOT null) FROM &quot;{row['table_name']}&quot; &quot;&quot;&quot;
                            cursor.execute(reset_statement)
                            return_values = dictfetchall(cursor)
                            # will be 1 row
                            for value in return_values:
                                print(f&quot;Sequence reset to {value['setval']} for {row['table_name']}&quot;)
                        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&quot;{row['table_name']} not reset&quot;)

            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, table_name)