PostgreSQL将JSONB字段中的数值更新为文本的问题求助

原更新语句无效的原因 #

你用的JSONB_SET(data, '{budget}', data->'budget'::text)之所以没生效，核心问题在于参数类型不匹配导致的隐式解析错误：

• data->'budget'返回的是jsonb类型的数值（比如500::jsonb），转成text后得到字符串'500'。
• JSONB_SET的第三个参数要求是jsonb类型，PostgreSQL会自动把传入的'500'解析为jsonb数值（而非字符串），所以最终写入的还是数值类型，没有变成字符串。

可行解决办法 #

1. 针对单个已知键（如budget）的更新 #

需要先提取数值的文本形式，再用to_jsonb()将其包装为jsonb字符串类型，确保写入后是字符串格式：

UPDATE my_table
SET data = JSONB_SET(data, '{budget}', to_jsonb(data->>'budget'))
WHERE data ? 'budget';

• data->>'budget'直接提取budget对应的文本值（从500::jsonb得到'500'::text）。
• to_jsonb()将普通文本转为jsonb字符串类型（即"500"::jsonb），这样JSONB_SET就能正确写入字符串值。

2. 批量处理所有数值类型的键（无需硬编码） #

如果表中JSONB列有大量不确定的数值键，无法逐个硬编码，可以用递归函数遍历整个JSONB结构，把所有数值类型转为字符串：
首先创建自定义函数：

CREATE OR REPLACE FUNCTION jsonb_numbers_to_text(jsonb_val jsonb)
RETURNS jsonb AS $$
BEGIN
  CASE jsonb_typeof(jsonb_val)
    WHEN 'object' THEN
      -- 递归处理对象的每个键值对
      RETURN (SELECT jsonb_object_agg(key, jsonb_numbers_to_text(value))
              FROM jsonb_each(jsonb_val));
    WHEN 'array' THEN
      -- 递归处理数组的每个元素
      RETURN (SELECT jsonb_agg(jsonb_numbers_to_text(element))
              FROM jsonb_array_elements(jsonb_val) AS element);
    WHEN 'number' THEN
      -- 将数值转为文本格式的jsonb
      RETURN to_jsonb(jsonb_val::text);
    ELSE
      -- 非数值类型直接返回原内容
      RETURN jsonb_val;
  END CASE;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

然后执行全表更新：

UPDATE my_table
SET data = jsonb_numbers_to_text(data);

这个函数会递归遍历JSONB的所有层级，自动将所有数值类型（整数、浮点数）转为字符串，完全不需要硬编码键名，适合数据量极大且结构复杂的场景。

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