Reset Sequence in Postgres

DO $$
    DECLARE
        r RECORD;
        max_val int;
    BEGIN
        FOR r IN
            SELECT sequence_namespace.nspname AS sequence_schema,
                   class_sequence.relname AS sequence_name,
                   table_namespace.nspname AS table_schema,
                   class_table.relname AS table_name,
                   pg_attribute.attname AS column_name
            FROM pg_depend
                     INNER JOIN pg_class AS class_sequence ON class_sequence.oid = pg_depend.objid AND class_sequence.relkind = 'S'
                     INNER JOIN pg_class AS class_table ON class_table.oid = pg_depend.refobjid
                     INNER JOIN pg_attribute ON pg_attribute.attrelid = class_table.oid AND pg_depend.refobjsubid = pg_attribute.attnum
                     INNER JOIN pg_namespace AS table_namespace ON table_namespace.oid = class_table.relnamespace
                     INNER JOIN pg_namespace AS sequence_namespace ON sequence_namespace.oid = class_sequence.relnamespace
            ORDER BY sequence_namespace.nspname, class_sequence.relname
            LOOP
                EXECUTE format('SELECT COALESCE(MAX(%I), 0) + 1 FROM %I.%I',
                               r.column_name,
                               r.table_schema,
                               r.table_name)
                    INTO max_val;

                EXECUTE format('ALTER SEQUENCE %I.%I RESTART WITH %s',
                               r.sequence_schema,
                               r.sequence_name,
                               max_val);
            END LOOP;
    END $$;

Meta

Created:

Updated: 2024-06-26 12:06