PostgreSQL中JSON字段更新语句正确性验证求助

你的UPDATE语句存在两处关键错误，以下是修正方案和生产环境安全操作建议：

错误分析 #

1. 字段名错误：表中不存在dataaccs_ftp字段，实际要更新的是name = 'dataaccs_ftp'对应的value字段。
2. jsonb_set语法错误：该函数的正确参数格式为jsonb_set(target_jsonb, path_array, new_value_jsonb)，你将路径和新值混写，不符合语法要求。

正确更新语句 #

情况1：value字段为jsonb类型 #

UPDATE parameter
SET value = jsonb_set(value, '{url}', '"newurl.url.com"')
WHERE name = 'dataaccs_ftp';

情况2：value字段为json类型（PostgreSQL 12+） #

如果字段是json类型，可直接使用json_set，或转换为jsonb操作后再转回：

-- 方式1：使用json_set
UPDATE parameter
SET value = json_set(value, '{url}', '"newurl.url.com"')
WHERE name = 'dataaccs_ftp';

-- 方式2：转jsonb操作
UPDATE parameter
SET value = jsonb_set(value::jsonb, '{url}', '"newurl.url.com"')::json
WHERE name = 'dataaccs_ftp';

生产环境安全操作建议 #

为避免误操作损坏数据，建议按以下步骤执行：

• 先备份数据：提前备份目标表

  CREATE TABLE parameter_backup AS SELECT * FROM parameter;
  

• 先验证修改结果：执行SELECT查看修改后的预期效果，确认无误再执行UPDATE

  SELECT name, jsonb_set(value::jsonb, '{url}', '"newurl.url.com"') AS updated_value
  FROM parameter
  WHERE name = 'dataaccs_ftp';
  

• 用事务测试：通过事务包裹操作，确认结果正确后再提交，否则回滚

  BEGIN;
  UPDATE parameter
  SET value = jsonb_set(value, '{url}', '"newurl.url.com"')
  WHERE name = 'dataaccs_ftp';
  -- 查看修改结果
  SELECT * FROM parameter WHERE name = 'dataaccs_ftp';
  -- 确认正确则执行COMMIT; 否则执行ROLLBACK;
  

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