PostgreSQL中如何批量修改表中多列的数据类型？

嘿，手动改50列确实太繁琐了，完全违背*DRY（Don't Repeat Yourself）*原则！下面给你两个高效的批量处理方案，不用重复写几十遍ALTER语句：

方法一：自动生成ALTER语句（推荐，更透明） #

先通过查询PostgreSQL的系统表，生成所有需要执行的ALTER语句，你可以先预览这些语句，确认无误后再执行：

SELECT 
  'ALTER TABLE your_table_name ALTER COLUMN ' || quote_ident(column_name) || ' TYPE numeric USING (' || quote_ident(column_name) || '::numeric);'
FROM information_schema.columns
WHERE 
  table_name = 'your_table_name' -- 替换成你的表名
  AND table_schema = 'public' -- 替换成你的schema（如果不是public的话）
  AND data_type = 'text'
  -- 可选：如果只有特定列需要转换，添加列名筛选条件
  AND column_name IN ('col1', 'col2', 'col3', ...); -- 把你的50列列名放这里

执行这个查询后，结果集里的每一行就是一条独立的ALTER语句。你可以把这些结果复制出来，一次性执行（或者分批次，避免锁表太久）。

方法二：用PL/pgSQL循环自动执行 #

如果你想让数据库自动完成所有修改，可以写一个匿名PL/pgSQL块，循环处理目标列：

DO $$
DECLARE
    col_record record;
BEGIN
    FOR col_record IN
        SELECT column_name
        FROM information_schema.columns
        WHERE 
          table_name = 'your_table_name'
          AND table_schema = 'public'
          AND data_type = 'text'
          AND column_name IN ('col1', 'col2', ...) -- 同样替换成你的列列表
    LOOP
        -- 动态执行ALTER语句，用quote_ident避免列名有特殊字符/关键字
        EXECUTE 'ALTER TABLE your_table_name ALTER COLUMN ' || quote_ident(col_record.column_name) || ' TYPE numeric USING (' || quote_ident(col_record.column_name) || '::numeric);';
    END LOOP;
END $$;

关键注意事项 #

• 数据合法性检查：转换前一定要确认这些text列里的内容都是合法的数字！可以用下面的语句排查非数字行：

  SELECT column_name, count(*) 
  FROM your_table_name 
  WHERE column_name !~ '^-?\d+(\.\d+)?$' -- 匹配整数和小数，可根据你的数据调整正则
  GROUP BY column_name;
  

  如果有非数字内容，转换会失败，得先清理这些数据。
• USING子句不能省略：PostgreSQL不会自动把text转成numeric，必须用USING明确指定转换规则。
• quote_ident()的必要性：如果你的列名包含空格、特殊字符或者是PostgreSQL关键字，quote_ident()会自动给列名加上引号，避免语法错误。

内容的提问来源于stack exchange，提问作者Glenn G.