pgsql批量修改sequences的start

修改为指定值

DO $$DECLARE r record;
BEGIN
FOR r IN SELECT sequence_name FROM information_schema."sequences"
LOOP
    EXECUTE 'ALTER SEQUENCE '|| r.sequence_name ||' restart WITH 10000';
END LOOP;
END$$;

根据表的id修改

DO $$
DECLARE 
   r record;
   start_value integer := 0;
BEGIN
FOR r IN SELECT tablename||'_id_seq' AS sequence_name, tablename FROM pg_tables WHERE schemaname = 'public'
LOOP
    EXECUTE 'SELECT max(id)+1 AS max_value FROM ' || r.tablename INTO start_value;
    IF start_value IS NULL THEN start_value:= 1;
    END IF;
    RAISE NOTICE 'start_value % %', r.tablename,start_value;
    EXECUTE 'ALTER SEQUENCE '|| r.sequence_name ||' restart WITH ' || start_value;
END LOOP;
END$$;