如何在PostgreSQL表的任意列中查找指定字符串并更新为null

前置提醒 #

• 操作前必须备份目标表，防止误操作导致数据丢失，备份语句：
  CREATE TABLE 目标表名_bak AS SELECT * FROM 目标表名;
• 以下操作仅处理字符串类型列，不会修改数值、日期等其他类型的字段。

方案1：手动指定列（安全优先，推荐） #

如果清楚目标表的所有字符串列名，直接编写UPDATE语句即可，示例参数：

• 目标表：user_info
• 异常字符串：'无效内容'
• 待检查列：username、email、desc
  对应SQL：

UPDATE user_info
SET 
  username = CASE WHEN username LIKE '%无效内容%' THEN NULL ELSE username END,
  email = CASE WHEN email LIKE '%无效内容%' THEN NULL ELSE email END,
  desc = CASE WHEN desc LIKE '%无效内容%' THEN NULL ELSE desc END
WHERE 
  username LIKE '%无效内容%' 
  OR email LIKE '%无效内容%'
  OR desc LIKE '%无效内容%';

如果需要精确匹配完全等于异常字符串的内容，将LIKE '%xxx%'替换为= 'xxx'；如果需要大小写不敏感匹配，将LIKE替换为ILIKE。

方案2：动态生成更新语句（适配列多/表结构未知场景） #

如果表的字符串列数量多，或者不清楚具体列名，可以通过系统表自动生成更新语句：

1. 执行以下查询，替换'目标表名'、'待匹配的异常字符串'为实际值：

SELECT 'UPDATE 目标表名 SET ' || 
  string_agg(quote_ident(column_name) || ' = CASE WHEN ' || quote_ident(column_name) || ' LIKE ''%待匹配的异常字符串%'' THEN NULL ELSE ' || quote_ident(column_name) || ' END', ', ') ||
  ' WHERE ' || 
  string_agg(quote_ident(column_name) || ' LIKE ''%待匹配的异常字符串%''', ' OR ') || ';'
FROM information_schema.columns 
WHERE table_name = '目标表名' 
  AND data_type IN ('character varying', 'character', 'text');

2. 上述查询会返回完整的UPDATE语句，核对列名、匹配规则无误后，执行生成的语句即可完成批量更新。

结果校验 #

更新完成后执行以下查询，确认是否还有残留的异常字符串：

SELECT * FROM 目标表名
WHERE 字符串列1 LIKE '%异常字符串%' OR 字符串列2 LIKE '%异常字符串%' OR 其余字符串列;

返回空结果即说明所有异常值已处理完毕。

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