reset-sequence.md

Fix sequence in PostgreSQL

CREATE OR REPLACE FUNCTION reset_sequences_to_max(schema varchar default 'public', dry_run bool default true)
RETURNS void AS $$
DECLARE
    r RECORD;
    query TEXT;
BEGIN
    -- Loop through all tables
    FOR r IN SELECT t.table_schema, t.table_name, column_name, sequence_name
             FROM information_schema.tables t
             JOIN information_schema.columns c ON t.table_name = c.table_name
             JOIN information_schema.sequences s ON 'nextval('''||s.sequence_name||'''::regclass)' = c.column_default
             WHERE t.table_schema = schema
    LOOP
        -- Construct the dynamic query to set the sequence value
        query := format('SELECT setval(''%I'', (SELECT MAX(%I) FROM %I.%I) - 1);',
                         r.sequence_name, r.column_name, r.table_schema, r.table_name);
        
        -- Execute the dynamic query
        if dry_run then
            raise notice'Run query: %', query;
        else
            EXECUTE query;
        end if;
        
    END LOOP;
END;
$$ LANGUAGE plpgsql;

cat > /tmp/reset.sql << EOL
 SELECT
     'SELECT SETVAL(' ||
        quote_literal(quote_ident(sequence_namespace.nspname) || '.' || quote_ident(class_sequence.relname)) ||
        ', COALESCE(MAX(' ||quote_ident(pg_attribute.attname)|| '), 1) ) FROM ' ||
        quote_ident(table_namespace.nspname)|| '.'||quote_ident(class_table.relname)|| ';'
 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;
 EOL
 
 -- export and run sql to fix sequences
 for db in $databases; do
     psql -Atq -f /tmp/reset.sql -d $db -o /tmp/$db.sql
     psql -f /tmp/$db.sql -d $db
 done