PostgreSQL插入JSONB数组报malformed array literal错误如何解决

• 你使用的花括号格式数组字面量，和JSON内部自带的双引号产生了语法冲突。PostgreSQL解析数组字面量时，会将JSON内容里的双引号误识别为数组元素的边界标记，最终判定你输入的数组字面量格式非法。

推荐使用ARRAY构造器写法，语法更清晰不易出错，无需额外转义字符：

INSERT INTO main(prev_snippets, next_snippets, req_characters, description, requirements, prereq_events, character_effects, world_effects, character_decisions)
VALUES (
  ARRAY[22, 45, 99], -- 整数数组直接传数值即可，不需要包裹引号
  ARRAY[33, 22],
  ARRAY[
    '{"traits":{"belief":[],"personality":[],"physical":[]},"relationships":[]}'::jsonb,
    '{"traits":{"belief":[],"personality":[],"physical":[]},"relationships":[]}'::jsonb
  ],
  'cvdfgfg',
  NULL, NULL, NULL, NULL, NULL
);

如果你坚持要使用花括号格式的数组字面量，需要将JSON内部的双引号全部转义（每一个双引号前加反斜杠，同时每个JSON元素整体用双引号包裹），这种写法容易出错不推荐，示例如下：

INSERT INTO main(prev_snippets, next_snippets, req_characters, description, requirements, prereq_events, character_effects, world_effects, character_decisions)
VALUES (
  '{22,45,99}',
  '{33,22}',
  '{"{\"traits\":{\"belief\":[],\"personality\":[],\"physical\":[]},\"relationships\":[]}","{\"traits\":{\"belief\":[],\"personality\":[],\"physical\":[]},\"relationships\":[]}"}'::jsonb[],
  'cvdfgfg',
  NULL, NULL, NULL, NULL, NULL
);

如果你的业务场景中不需要对数组内的单个JSONB元素做单独的索引、查询等操作，也可以考虑将req_characters等字段的类型直接改为JSONB，直接存储JSON格式的数组，写入语法更简单，示例：

-- 字段类型改为JSONB后的写入示例
INSERT INTO main(req_characters) VALUES ('[{"traits":{"belief":[],"personality":[],"physical":[]},"relationships":[]},{"traits":{"belief":[],"personality":[],"physical":[]},"relationships":[]}]');

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