如何在PostgreSQL嵌套jsonb列中设置时间戳键值对？

问题场景 #

尝试用jsonb_set给JSONB类型的ack列设置带时间戳的键值对，普通字符串值可正常运行，但插入时间戳时触发语法错误：

正常运行的脚本 #

UPDATE TABLE_NAME set ack=jsonb_set(ack , '{clientZ}', '{"timestamp": "ABCDE"}')

执行结果：

{"clientZ": {"timestamp": "ABCDE"}}

报错的脚本 #

UPDATE TABLE_NAME set ack=jsonb_set(ack , '{clientZ}', '{"timestamp": to_jsonb(to_char(now(), ''YYYY-MM-DD HH:MI:SS.MS TZHTZM''))}')

期望结果：

{"clientZ": {"timestamp": "2024-10-30 11:31:38.765 -0500"}}

错误信息 #

SQL Error [42601]: ERROR: syntax error at or near "to_jsonb"

错误原因 #

你将PostgreSQL的函数（to_jsonb、to_char、now()）直接写入JSON字符串字面量中，PostgreSQL会把整个内容当作普通字符串处理，不会解析其中的SQL函数，因此触发语法错误。

修正方案 #

需要先通过jsonb_build_object构造出符合要求的JSONB对象，再传入jsonb_set：

UPDATE TABLE_NAME 
SET ack = jsonb_set(
    ack, 
    '{clientZ}', 
    jsonb_build_object(
        'timestamp', 
        to_char(now(), 'YYYY-MM-DD HH:MI:SS.MS TZHTZM')
    )
);

也可以用类型转换的方式，先拼接时间戳字符串再转成JSONB（推荐第一种方式，更安全）：

UPDATE TABLE_NAME 
SET ack = jsonb_set(
    ack, 
    '{clientZ}', 
    ('{"timestamp": "' || to_char(now(), 'YYYY-MM-DD HH:MI:SS.MS TZHTZM') || '"}')::jsonb
);

两种方式都会先执行SQL函数生成时间戳字符串，再构造出正确的JSONB对象，最终得到期望结果。

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