reset_sequences.py

from django.db import connection
from django.db.utils import DatabaseError

def reset_sequences():
    try:
        with connection.cursor() as cursor:
            # First get the sequences and their associated columns
            cursor.execute("""
                SELECT 
                    n.nspname as schema_name,
                    S.relname as sequence_name,
                    T.relname as table_name,
                    C.attname as column_name
                FROM pg_class AS S
                    JOIN pg_namespace n ON n.oid = S.relnamespace
                    JOIN pg_depend AS D ON S.oid = D.objid
                    JOIN pg_class AS T ON D.refobjid = T.oid
                    JOIN pg_attribute AS C ON (D.refobjid = C.attrelid
                        AND D.refobjsubid = C.attnum)
                WHERE S.relkind = 'S'
                    AND n.nspname NOT LIKE 'pg_%'
                    AND n.nspname != 'information_schema'
                ORDER BY schema_name, sequence_name;
            """)
            
            sequences = cursor.fetchall()
            
            # Generate and execute SETVAL statements
            for schema, sequence, table, column in sequences:
                reset_query = f"""
                    SELECT SETVAL(
                        '{schema}.{sequence}',
                        COALESCE((SELECT MAX({column}) FROM {schema}.{table}), 1),
                        false
                    );
                """
                try:
                    cursor.execute(reset_query)
                except DatabaseError as e:
                    print(f"Error resetting sequence {schema}.{sequence}: {str(e)}")
                    continue
                    
        print("All sequences have been reset.")
        
    except DatabaseError as e:
        print(f"Database error occurred: {str(e)}")
        raise

# To run this function, call:
# reset_sequences()