如何用PostgreSQL函数批量将指定字符串替换为NULL？

问题根源分析 #

1. 第一个函数：使用REPLACE替换为空字符串，但空字符串''与NULL在PostgreSQL中是完全不同的值，因此无法满足需求。
2. 第二个函数：REPLACE函数若第三个参数为NULL，会直接返回NULL（PostgreSQL中函数参数为NULL时返回NULL），导致整列所有值被覆盖为NULL，而非仅替换目标字符串。
3. 传NULL报错：字符串拼接时，NULL会使整个SQL语句变为NULL，触发EXECUTE的参数非空校验错误。

正确实现方案 #

推荐使用NULLIF函数（当两个值相等时返回NULL，否则返回原值），结合参数化执行避免SQL注入和拼接问题，同时仅处理字符类型列（数值列不会存储目标字符串）。

方案1：逐列更新（适合小表）

CREATE OR REPLACE FUNCTION replace_string_to_null(_table text, _old_val text)
RETURNS void AS $$
DECLARE
    col record;
    update_sql text;
BEGIN
    FOR col IN
        SELECT column_name
        FROM information_schema.columns
        WHERE table_schema = 'public'
          AND table_name = _table
          AND data_type IN ('text', 'character varying', 'character')
    LOOP
        update_sql := format('UPDATE %I SET %I = NULLIF(%I, $1)', _table, col.column_name, col.column_name);
        EXECUTE update_sql USING _old_val;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

调用方式：

SELECT replace_string_to_null('eavs2014', '-999999NA');

方案2：单次批量更新（适合大表，效率更高）

构造包含所有目标列的SET子句，仅执行一次UPDATE，减少表扫描次数：

CREATE OR REPLACE FUNCTION replace_string_to_null_single_update(_table text, _old_val text)
RETURNS void AS $$
DECLARE
    set_clause text;
    update_sql text;
BEGIN
    SELECT string_agg(format('%I = NULLIF(%I, $1)', column_name, column_name), ', ')
    INTO set_clause
    FROM information_schema.columns
    WHERE table_schema = 'public'
      AND table_name = _table
      AND data_type IN ('text', 'character varying', 'character');

    IF set_clause IS NULL THEN
        RETURN;
    END IF;

    update_sql := format('UPDATE %I SET %s', _table, set_clause);
    EXECUTE update_sql USING _old_val;
END;
$$ LANGUAGE plpgsql;

调用方式：

SELECT replace_string_to_null_single_update('eavs2014', '-999999NA');

关键说明 #

• 使用format函数的%I标识符转义，避免表名/列名含特殊字符或SQL注入风险。
• NULLIF函数精准匹配目标字符串，仅替换符合条件的值为NULL，保留其他值不变。
• 过滤字符类型列，避免对数值列执行无效操作，提升效率。

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