如何在PostgreSQL中批量更新损坏的JSON字面量字段？

你的错误是因为部分iws_location记录的内容并非标准的{'address': 'xxx'}格式，比如直接是'DUQUE'这类纯文本。当你替换单引号为双引号后，得到的是"DUQUE"——这是一个JSON字符串而非JSON对象，此时用->> 'address'提取键值就会触发JSON语法错误，因为字符串类型不支持键提取操作。

下面提供两种可靠的解决方法：

方法一：用正则表达式直接提取（推荐，避免JSON转换风险） #

通过正则匹配目标格式的字符串，直接提取address对应的值，无需转换JSON，兼容性更强：

UPDATE web_scraping.iws_info_web_scraping
SET iws_location = regexp_replace(iws_location, '^\{''address'': ''(.*?)''\}$', '\1')
WHERE iws_id >= 1
AND iws_location ~ '^\{''address'': ''(.*?)''\}$';

• 正则^\{''address'': ''(.*?)''\}$精准匹配{'address': 'xxx'}格式的内容
• \1引用匹配到的xxx部分，直接替换原字段值
• WHERE条件确保只处理符合目标格式的记录，避免误改其他内容

方法二：用安全JSON转换处理（适用于PostgreSQL 12+） #

利用TRY_CAST函数捕获JSON转换失败的情况，仅处理能合法转换为含address键的JSON对象的记录：

UPDATE web_scraping.iws_info_web_scraping
SET iws_location = TRY_CAST(replace(iws_location, '''', '"') AS json) ->> 'address'
WHERE iws_id >= 1
AND TRY_CAST(replace(iws_location, '''', '"') AS json) IS NOT NULL
AND TRY_CAST(replace(iws_location, '''', '"') AS json) ? 'address'
AND iws_location IS DISTINCT FROM TRY_CAST(replace(iws_location, '''', '"') AS json) ->> 'address';

• TRY_CAST在转换失败时返回NULL，避免触发语法错误
• ? 'address'判断JSON对象是否包含address键
• 最后一个条件确保只更新需要修改的记录，避免无意义的写入

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