PostgreSQL UPDATE语句报syntax error at or near "["错误求助

错误核心原因 #

你的SQL语句存在两个致命语法问题：

1. 字符串拼接单引号未正确闭合：在'lastFailure ": '[{"@t": "DateTime", "@value": "$1"}]部分，前面的字符串结束于单引号，后续的[未被包含在字符串中，PostgreSQL无法识别该语法，直接触发错误。
2. 参数传递方式错误：直接将$1写入拼接字符串会被当作普通文本，而非预编译参数，同时手动拼接json格式极易出现引号、括号不匹配的问题。

另外，通过字符串拼接生成jsonb的方式容错性极低，任何格式细节错误都会导致语句执行失败，推荐使用PostgreSQL原生jsonb操作函数处理。

修正方案 #

方案1：使用jsonb原生函数（推荐） #

利用jsonb_set精准更新指定字段，结合jsonb_build_object/jsonb_build_array安全构造json结构：

UPDATE s 
SET sb = jsonb_set(
           sb, 
           '{filded,0,@value}', 
           ((sb->'filded'->0->>'@value')::integer + 1)::text::jsonb
         )
         || jsonb_build_object(
             'lastFailure', jsonb_build_array(jsonb_build_object('@t', 'DateTime', '@value', $1)),
             'ln', jsonb_build_array(jsonb_build_object('@type', 'DateTime', '@value', $1))
         )
WHERE sbi = $2;

• jsonb_set：精准定位并更新filded数组第一个元素的@value值，自动处理类型转换。
• jsonb_build_object/jsonb_build_array：安全生成jsonb结构，彻底避免手动拼接的格式错误。
• 正确使用预编译参数$1/$2，既保证参数传递准确，又防止SQL注入风险。

方案2：修复字符串拼接语法（不推荐） #

如果坚持使用字符串拼接，需修正单引号闭合问题并正确传递参数：

UPDATE s SET sb = sb ||
              ('{"filded": [{"@value": ' ||
              (sb@>>'{filded,0,@value}')::integer + 1 ||
              '}],"lastFailure": [{"@t": "DateTime", "@value": "' || $1 || '"}],"ln": [{"@type": "DateTime", "@value": "' || $1 || '"}]}')::jsonb
WHERE sbi=$2;

注意：

• 确保所有嵌套json结构被完整包含在单引号内，避免语法断裂。
• 参数$1需通过||拼接进字符串（若$1是日期类型，需先通过to_char($1, 'YYYY-MM-DD"T"HH24:MI:SS')转换为符合json格式的文本）。
• 该方式仅适用于简单场景，复杂json结构极易出错。

关键提示 #

• 优先使用PostgreSQL提供的jsonb_*系列函数操作jsonb数据，可读性与稳定性远高于字符串拼接。
• 确认预编译参数$1/$2的类型与目标字段匹配，避免类型转换错误。

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