Postgre - Restore Auto Increment

reset_all_sequences.md

CREATE OR REPLACE FUNCTION reset_all_sequences()
RETURNS VOID AS $$
DECLARE
    table_name_var TEXT;
    column_name TEXT;
BEGIN
    -- Loop through all tables in the database
    FOR table_name_var IN (SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_type = 'BASE TABLE') LOOP
        -- Find the name of the sequence associated with the auto-increment column
        EXECUTE 'SELECT column_name FROM information_schema.columns WHERE table_name = $1 AND column_default LIKE $2'
        INTO column_name
        USING table_name_var, 'nextval%';

        -- If a sequence is found, reset it to the current maximum value in the column + 1
        IF column_name IS NOT NULL THEN
            EXECUTE 'SELECT setval(pg_get_serial_sequence($1, $2), (SELECT MAX(' || column_name || ') FROM ' || table_name_var || ') + 1)'
            USING table_name_var, column_name;
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql;