如何批量更新PostgreSQL的JSONB列中键名subtype为subType？

我有一个PostgreSQL表，结构如下：

info列存储的JSON数据示例：

{"id": "1402417796043342360",  "colour": "blue", "subtype": "test", "description": "8.7"}

需要批量修改所有行的info列，将键名subtype替换为subType。尝试执行以下SQL时触发报错：

UPDATE table_name SET info = REPLACE('info', '"subtype"', '"subType"');

报错信息：

ERROR: column "info" is of type jsonb but expression is of type text
LINE 1: UPDATE table_name SET info = REPLACE('info', '"su...
^
HINT: You will need to rewrite or cast the expression.

方法1：使用JSONB原生操作（PostgreSQL 10+，推荐） #

这种方法直接操作JSONB结构，不会误改值中的内容，效率和安全性都更高：

UPDATE table_name
SET info = jsonb_set(info - 'subtype', '{subType}', info->'subtype')
WHERE info ? 'subtype'; -- 仅更新包含subtype键的行，避免无效操作

• info - 'subtype'：删除原JSONB中的subtype键
• jsonb_set(..., '{subType}', info->'subtype')：将原subtype对应的值赋值给新键subType
• WHERE子句过滤无需更新的行，提升性能

方法2：文本转换法（兼容所有PostgreSQL版本，谨慎使用） #

如果你的PostgreSQL版本较低，可以将JSONB转为文本替换后再转回，但注意：如果subtype出现在JSON的值中也会被替换，仅在确定键名不会出现在值里时使用：

UPDATE table_name
SET info = REPLACE(info::text, '"subtype":', '"subType":')::jsonb
WHERE info::text LIKE '%"subtype":%';

• info::text：将JSONB类型转为文本字符串
• REPLACE(..., '"subtype":', '"subType":')：精准匹配键名（带冒号避免误匹配值）
• ::jsonb：将处理后的文本转回JSONB类型

方法3：遍历键值对重构JSONB（通用安全方案） #

通过拆分JSONB的键值对进行处理，完全避免误替换的风险：

UPDATE table_name
SET info = (
  SELECT jsonb_object_agg(
    CASE WHEN key = 'subtype' THEN 'subType' ELSE key END,
    value
  )
  FROM jsonb_each(info)
)
WHERE info ? 'subtype';

• jsonb_each(info)：将JSONB对象拆分为键值对的行集合
• CASE语句替换目标键名，其他键保持不变
• jsonb_object_agg：将处理后的键值对重新聚合为JSONB对象

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