PostgreSQL如何批量修改指定Schema下所有表同名字段类型为boolean

前置操作提示 #

• 操作前务必全量备份数据库，避免转换异常导致数据丢失
• 建议先在测试环境验证转换逻辑，确认无误后再操作生产环境
• 大表执行ALTER TABLE操作会触发表锁，建议选择业务低峰期执行

实现方案 #

方案1：手动生成批量修改SQL执行 #

通过查询系统表批量拼接出所有表的修改语句，复制后直接执行即可：

SELECT 'ALTER TABLE "' || table_schema || '"."' || table_name || '" ALTER COLUMN y_column TYPE BOOLEAN USING (y_column::int::boolean);'
FROM information_schema.columns
WHERE table_schema = 'postgre_x_schema'
  AND column_name = 'y_column'
  AND data_type = 'numeric';

方案2：存储过程自动执行所有转换 #

无需手动复制SQL，直接执行以下PL/pgSQL脚本自动完成所有表的转换：

DO $$
DECLARE
    v_table_name text;
BEGIN
    -- 遍历所有符合条件的表
    FOR v_table_name IN
        SELECT table_name
        FROM information_schema.columns
        WHERE table_schema = 'postgre_x_schema'
          AND column_name = 'y_column'
          AND data_type = 'numeric'
    LOOP
        -- 执行字段类型转换，%I自动转义表名避免特殊字符报错
        EXECUTE format('ALTER TABLE postgre_x_schema.%I ALTER COLUMN y_column TYPE BOOLEAN USING (y_column::int::boolean)', v_table_name);
        RAISE NOTICE '已完成表%的y_column字段转换', v_table_name;
    END LOOP;
END $$;

异常数据排查 #

如果不确定字段是否存在非0/1的无效值，可以先执行以下脚本排查：

DO $$
DECLARE
    v_table_name text;
    v_abnormal_count bigint;
BEGIN
    FOR v_table_name IN
        SELECT table_name
        FROM information_schema.columns
        WHERE table_schema = 'postgre_x_schema'
          AND column_name = 'y_column'
          AND data_type = 'numeric'
    LOOP
        EXECUTE format('SELECT count(*) FROM postgre_x_schema.%I WHERE y_column NOT IN (0,1)', v_table_name) INTO v_abnormal_count;
        IF v_abnormal_count > 0 THEN
            RAISE NOTICE '表%存在%条无效数据，值不在0/1范围内', v_table_name, v_abnormal_count;
        END IF;
    END LOOP;
END $$;

转换逻辑说明：USING (y_column::int::boolean) 先将numeric类型转为int，PostgreSQL原生规则下int转boolean时自动对应1为TRUE、0为FALSE，完全匹配需求规则。

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