如何自动遍历PostgreSQL表所有列执行带CASE逻辑的UPDATE语句

核心方案：用动态SQL遍历列并执行更新 #

你可以通过构造动态SQL实现批量更新，直接在DO块中遍历目标列、生成对应UPDATE语句并执行。以下是完整可运行的代码：

DO
$$
DECLARE
    ColName text;
    TargetTable text := 'TabName'; -- 替换为你的实际表名
BEGIN 
    FOR ColName IN 
        SELECT column_name  
        FROM information_schema.columns 
        WHERE table_schema = 'public' 
          AND table_name = TargetTable
          AND data_type = 'text' -- 仅处理TEXT类型列，避免非目标列报错
    LOOP
        -- 构造动态UPDATE语句，用format函数安全引用标识符
        EXECUTE format(
            'UPDATE %I
             SET %I = CASE %I
                 WHEN ''Strongly disagree'' THEN ''1''
                 WHEN ''Disagree'' THEN ''2''
                 WHEN ''Indifferent'' THEN ''3''
                 WHEN ''Agree'' THEN ''4''
                 WHEN ''Strongly agree'' THEN ''5''
                 WHEN ''#NULL!'' THEN NULL
                 WHEN '''' THEN NULL
                 ELSE %I
             END
             WHERE %I IS NOT NULL;',
            TargetTable,
            ColName, ColName, ColName, ColName
        );
        
        -- 可选：输出已处理的列名，用于调试
        RAISE NOTICE '已更新列: %', ColName;
    END LOOP;
END;
$$;

关键细节说明 #

1. 安全处理标识符：用format函数的%I占位符自动转义列名和表名，避免因列名含空格、特殊字符或关键字导致语法错误。
2. 限定目标列类型：在查询information_schema.columns时添加data_type = 'text'条件，确保只处理需要更新的TEXT类型列。
3. 动态执行逻辑：EXECUTE是PostgreSQL中处理动态表/列名的唯一可行方式——PREPARE语句不支持将标识符作为参数传入，必须通过字符串拼接生成完整SQL后执行。

封装为可复用函数（可选） #

如果需要在多个表上重复执行该逻辑，可以封装成自定义函数：

CREATE OR REPLACE FUNCTION batch_update_text_columns(p_table_name text)
RETURNS void AS
$$
DECLARE
    ColName text;
BEGIN 
    FOR ColName IN 
        SELECT column_name  
        FROM information_schema.columns 
        WHERE table_schema = 'public' 
          AND table_name = p_table_name
          AND data_type = 'text'
    LOOP
        EXECUTE format(
            'UPDATE %I
             SET %I = CASE %I
                 WHEN ''Strongly disagree'' THEN ''1''
                 WHEN ''Disagree'' THEN ''2''
                 WHEN ''Indifferent'' THEN ''3''
                 WHEN ''Agree'' THEN ''4''
                 WHEN ''Strongly agree'' THEN ''5''
                 WHEN ''#NULL!'' THEN NULL
                 WHEN '''' THEN NULL
                 ELSE %I
             END
             WHERE %I IS NOT NULL;',
            p_table_name,
            ColName, ColName, ColName, ColName
        );
        RAISE NOTICE '已更新列: %', ColName;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- 使用方式：
-- SELECT batch_update_text_columns('你的表名');

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