如何在PostgreSQL中更新含格式错误JSON字面量的文本字段？

单个列的更新方法 #

如果只需更新某一列（比如location列），直接用regexp_replace结合正则即可完成：

UPDATE your_table
SET location = regexp_replace(location, E'^\'address\': \'(.*?)\'$', '\1', 'g')
-- 仅更新符合目标格式的行，避免无意义操作
WHERE location ~ E'^\'address\': \'.*?\'';

正则说明 #

• E'^\'address\': \'(.*?)\'$'：精准匹配以'address': '开头、单引号结尾的字符串，(.*?)非贪婪捕获地址内容（如New Mexico）
• '\1'：将匹配内容替换为第一个捕获组的结果，也就是我们需要提取的地址文本
• 'g'：全局替换标识（单条记录仅一个匹配，加不加不影响结果）

批量更新所有符合条件的列 #

如果要一次性处理表中所有character varying类型且内容符合该格式的列，用PostgreSQL动态SQL自动生成更新语句：

DO $$
DECLARE
    col_name text;
    table_name text := 'your_table'; -- 替换为你的实际表名
BEGIN
    -- 遍历表中所有字符类型列
    FOR col_name IN 
        SELECT column_name
        FROM information_schema.columns
        WHERE table_name = table_name
          AND data_type = 'character varying'
    LOOP
        -- 动态执行更新逻辑
        EXECUTE format(
            'UPDATE %I SET %I = regexp_replace(%I, E''^\'address\': \'(.*?)\'$'', ''\1'', ''g'') WHERE %I ~ E''^\'address\': \'.*?\''''',
            table_name, col_name, col_name, col_name
        );
    END LOOP;
END $$;

注意事项 #

1. 更新前务必备份数据，防止操作失误导致数据丢失
2. 把代码中的your_table替换成你实际要操作的表名
3. 若只需处理部分列，可在information_schema.columns的查询中添加过滤条件，比如column_name IN ('col1', 'col2')

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