DO $$
DECLARE
    rec RECORD;
    max_id bigint;
    seq_name TEXT;
    sql_str TEXT;
BEGIN
    -- 遍历每个表
    FOR rec IN
        SELECT table_name, column_name
        FROM information_schema.columns
        WHERE table_schema = 'manage' -- 或者你的模式
            AND column_default LIKE 'nextval(%' -- 确保只选择包含序列的列
    LOOP
        -- 构建序列名
        seq_name := rec.table_name || '_' || rec.column_name || '_seq';

        -- 检查序列是否存在
        IF EXISTS (
            SELECT 1 FROM information_schema.sequences
            WHERE sequence_schema = 'manage' -- 或者你的模式
                AND sequence_name = seq_name
        ) THEN
            -- 获取每个表的最大ID
            EXECUTE 'SELECT MAX(' || quote_ident(rec.column_name) || ') FROM manage.' || quote_ident(rec.table_name) INTO max_id;

            -- 设置序列的当前值
            sql_str := 'SELECT setval(' || quote_literal('manage.'||seq_name) || ', ' || (max_id + 1) || ')';
            IF sql_str IS NOT NULL THEN
                EXECUTE sql_str;
            END IF;
        ELSE
            RAISE NOTICE '序列 % 不存在', seq_name;
        END IF;
    END LOOP;
END $$;

发表于  2024-02-08 11:52:48 并被添加「」标签，阅读量： 2681 。