PostgreSQL中重置指定Schema序列同步主键最大值的脚本调试求助

原脚本失效的核心问题 #

1. 表名获取逻辑错误：当目标Schema下存在多个表时，SELECT table_name FROM information_schema.tables WHERE table_schema ='$TARGET_SCHEMA'会返回多行结果，直接赋值给tablename会触发SQL错误；且该逻辑完全没有建立序列与对应表的关联，所有序列都会绑定到同一个随机表，导致序列值设置错误。
2. 缺少序列与表/列的关联逻辑：原脚本没有通过系统表或内置函数定位序列对应的主键列，完全是盲目匹配。

修复后的脚本（适配SERIAL/IDENTITY类型主键） #

DO $$
DECLARE
    seq RECORD;
    rel_info RECORD;
    max_value BIGINT;
BEGIN
    -- 遍历目标Schema下的所有序列
    FOR seq IN (SELECT schemaname, sequencename FROM pg_sequences WHERE schemaname = '$TARGET_SCHEMA') LOOP
        -- 获取序列关联的表和列（pg_get_serial_sequence返回格式为"schema.table.column"）
        SELECT 
            regexp_split_to_array(pg_get_serial_sequence(quote_ident(seq.schemaname) || '.' || quote_ident(seq.sequencename), ''), '\.') AS rel_parts
        INTO rel_info;

        -- 如果序列关联了有效的表和列
        IF rel_info.rel_parts IS NOT NULL AND array_length(rel_info.rel_parts, 1) = 3 THEN
            -- 计算该列的最大值，没有数据则取0
            EXECUTE format(
                'SELECT COALESCE(MAX(%I), 0) FROM %I.%I',
                rel_info.rel_parts[3],
                rel_info.rel_parts[1],
                rel_info.rel_parts[2]
            ) INTO max_value;

            -- 设置序列值为最大值+1，false表示下一次调用nextval返回该值
            EXECUTE format(
                'SELECT setval(%L, %s, false)',
                quote_ident(seq.schemaname) || '.' || quote_ident(seq.sequencename),
                max_value + 1
            );
        END IF;
    END LOOP;
END $$;

使用说明 #

1. 将脚本中的$TARGET_SCHEMA替换为你的目标Schema名称（例如public）。
2. 以拥有该Schema权限的用户身份执行该脚本。

适配手动绑定序列的版本 #

如果序列是手动绑定到主键列（非SERIAL/IDENTITY创建），可使用以下脚本：

DO $$
DECLARE
    seq RECORD;
    tablename TEXT;
    columnname TEXT;
    max_value BIGINT;
BEGIN
    FOR seq IN (SELECT schemaname, sequencename FROM pg_sequences WHERE schemaname = '$TARGET_SCHEMA') LOOP
        -- 通过系统表找到序列关联的表和列
        SELECT 
            c.relname AS table_name,
            a.attname AS column_name
        INTO tablename, columnname
        FROM pg_depend d
        JOIN pg_class c ON d.objid = c.oid
        JOIN pg_attribute a ON d.refobjid = a.attrelid AND d.refobjsubid = a.attnum
        WHERE d.refclassid = 'pg_class'::regclass
          AND d.classid = 'pg_class'::regclass
          AND d.objid = quote_ident(seq.schemaname) || '.' || quote_ident(seq.sequencename)::regclass
          AND c.relkind = 'r' -- 只关联普通表
          AND c.relnamespace = seq.schemaname::regnamespace
        LIMIT 1;

        IF tablename IS NOT NULL AND columnname IS NOT NULL THEN
            EXECUTE format(
                'SELECT COALESCE(MAX(%I), 0) FROM %I.%I',
                columnname,
                seq.schemaname,
                tablename
            ) INTO max_value;

            EXECUTE format(
                'SELECT setval(%L, %s, false)',
                quote_ident(seq.schemaname) || '.' || quote_ident(seq.sequencename),
                max_value + 1
            );
        END IF;
    END LOOP;
END $$;

内容的提问来源于stack exchange，提问作者Henry Cai