PostgreSQL 13批量修改表text列为numeric：报错解决与可传参函数实现

• 报错relation "hugo" does not exist的核心触发原因有三点：
  1. 元数据查询漏匹配schema规则：原代码查询information_schema.columns时仅过滤了表名hugo，未指定所在schema为import，无法精准定位目标列
  2. 动态SQL拼接缺少schema前缀：拼接ALTER语句时直接使用裸表名hugo，PostgreSQL会按当前会话的search_path顺序查找对应表，无法定位不在默认路径下、属于import schema的表
  3. 转换逻辑缺失：原动态SQL没有嵌入手动修改时用到的USING (列名::numeric)显式转换规则，即使表定位正确，存在存量text数据时也会因无隐式转换规则执行失败

如果不需要封装成通用函数，仅对指定表执行批量修改，修正后的可直接运行的DO块代码如下：

DO $$
DECLARE
    t record;
BEGIN
    FOR t IN 
        SELECT column_name
        FROM information_schema.columns
        WHERE 
            table_schema = 'import'
            AND table_name = 'hugo'
            AND data_type = 'text'
    LOOP
        -- 拼接带显式转换逻辑的ALTER语句
        EXECUTE format(
            'ALTER TABLE %I.%I ALTER COLUMN %I TYPE numeric USING (%I::numeric)',
            'import',
            'hugo',
            t.column_name,
            t.column_name
        );
    END LOOP;
END $$;

按照需求封装支持传入目标表名、可直接通过SELECT调用的批量修改函数：

CREATE OR REPLACE FUNCTION batch_text_to_numeric(qualified_target_table text)
RETURNS void AS $$
DECLARE
    v_schema text;
    v_table text;
    t record;
BEGIN
    -- 拆分传入的全限定表名，兼容「schema.表名」「仅表名」两种传入格式
    IF qualified_target_table LIKE '%.%' THEN
        v_schema := split_part(qualified_target_table, '.', 1);
        v_table := split_part(qualified_target_table, '.', 2);
    ELSE
        -- 未传入schema时默认匹配public schema下的表
        v_schema := 'public';
        v_table := qualified_target_table;
    END IF;

    -- 循环遍历匹配到的text类型列
    FOR t IN 
        SELECT column_name
        FROM information_schema.columns
        WHERE 
            table_schema = v_schema
            AND table_name = v_table
            AND data_type = 'text'
    LOOP
        -- 生成带USING转换逻辑的ALTER语句
        EXECUTE format(
            'ALTER TABLE %I.%I ALTER COLUMN %I TYPE numeric USING (%I::numeric)',
            v_schema,
            v_table,
            t.column_name,
            t.column_name
        );
    END LOOP;
END;
$$ LANGUAGE plpgsql;

调用方式 #

• 对import schema下的hugo表执行批量修改：

SELECT batch_text_to_numeric('import.hugo');

• 对public schema下的指定表执行批量修改：

SELECT batch_text_to_numeric('orders');

• 代码中使用%I做标识符转义，可兼容带特殊字符、大小写敏感的表名、列名，避免纯字符串拼接导致的语法错误
• 转换逻辑与手动单条修改的逻辑完全一致，存量text数据会自动执行text::numeric的显式转换，无需额外预处理
• 若表中text列存在无法转换为numeric的非法内容（如包含非数字字符、中文的文本），执行时会抛出明确的转换错误，可根据报错定位对应列完成数据清洗后再执行
• 函数默认以当前执行用户的权限运行，可确保用户对目标表拥有ALTER权限时正常完成修改

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