PostgreSQL 13：50张同结构表批量执行多操作求助

首先确保目标 schema 存在：

CREATE SCHEMA IF NOT EXISTS ign_v2;

接下来用 PL/pgSQL 匿名块批量处理所有表，一次完成列添加、重命名和 schema 迁移：

DO $$
DECLARE
    rec RECORD;
BEGIN
    -- 遍历 ign schema 下的所有基表
    FOR rec IN SELECT table_name FROM information_schema.tables WHERE table_schema = 'ign' AND table_type = 'BASE TABLE' LOOP
        EXECUTE format(
            'ALTER TABLE ign.%I
             -- 添加 date 列并自动填充值
             ADD COLUMN date DATE DEFAULT ''2021-06-15''::DATE,
             -- 添加 source 列并自动填充值
             ADD COLUMN source VARCHAR(50) DEFAULT ''ign'',
             -- 重命名表（添加前缀后缀）
             RENAME TO %I,
             -- 迁移到 ign_v2 schema
             SET SCHEMA ign_v2;',
            rec.table_name,
            'IGN_bdTopo_' || rec.table_name || '_V1'
        );
    END LOOP;
END $$;

关键说明 #

1. 安全生成SQL：用 format() 函数和 %I 占位符处理表名，避免SQL注入问题，同时自动转义含特殊字符的表名。
2. 高效填充数据：通过 DEFAULT 子句添加列时直接填充值，比事后执行 UPDATE 更高效，尤其适合大表。
3. 迁移所有附属对象：SET SCHEMA 会自动将表的约束、索引、触发器等所有关联对象一并迁移到 ign_v2，无需额外操作。

测试建议 #

先手动测试单张表验证效果，确认无误后再执行批量脚本：

-- 替换成你的测试表名
ALTER TABLE ign.your_test_table
ADD COLUMN date DATE DEFAULT '2021-06-15'::DATE,
ADD COLUMN source VARCHAR(50) DEFAULT 'ign',
RENAME TO IGN_bdTopo_your_test_table_V1,
SET SCHEMA ign_v2;

-- 检查结果
SELECT * FROM ign_v2.IGN_bdTopo_your_test_table_V1 LIMIT 10;

可选操作：移除列默认值 #

如果后续插入数据时不需要自动填充 date 和 source 列，可以批量移除默认值：

DO $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN SELECT table_name FROM information_schema.tables WHERE table_schema = 'ign_v2' AND table_name LIKE 'IGN_bdTopo_%_V1' LOOP
        EXECUTE format(
            'ALTER TABLE ign_v2.%I
             ALTER COLUMN date DROP DEFAULT,
             ALTER COLUMN source DROP DEFAULT;',
            rec.table_name
        );
    END LOOP;
END $$;

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