使用自定义格式对象数组更新表失败，咨询适配的数据类型

问题背景 #

你的业务逻辑是：

• 用户点击其他用户时，chats表生成包含sender_id、reciever_id的空消息记录
• 用户发送消息时，更新对应记录的消息内容

当前使用jsonb[]类型存储消息，但执行UPDATE语句时报错，核心原因是你传入的消息格式不符合标准JSON规范——标准JSON要求对象的键必须用双引号包裹，而你的消息键（如message、sender）未添加引号。

适配自定义格式的可行方案 #

1. 改用text类型直接存储

如果不想修改消息格式，最简单的处理方式是将messages字段类型改为text，直接存储原始格式的字符串：

-- 先修改表结构
ALTER TABLE chats ALTER COLUMN messages TYPE text;

-- 执行UPDATE（用美元引号避免单引号转义问题）
UPDATE chats SET 
messages = $$[
  { message: 'hi', sender: 'test', reciever: null },
  { message: 'hello', sender: 'test', reciever: null }
]$$ WHERE 
sender_id = 47 AND 
reciever_id = 43;

后续读取数据时，可在应用层使用支持宽松JSON解析的工具处理（比如Python的demjson库、前端可通过eval或专用解析库处理）。

2. 转换为标准JSON存入jsonb[]

如果想保留jsonb[]的查询、索引等原生优势，建议将消息转换为标准JSON格式后再插入，修正后的UPDATE语句如下：

UPDATE chats SET 
messages = '[
  { "message": "hi", "sender": "test", "reciever": null },
  { "message": "hello", "sender": "test", "reciever": null }
]'::jsonb[] WHERE 
sender_id = 47 AND 
reciever_id = 43;

这是长期来看更规范的方案，能充分利用PostgreSQL对JSON类型的各项原生支持。

3. 自定义数据类型（进阶）

如果必须保留原始格式且需要数据库层面的解析能力，可以在PostgreSQL中创建自定义数据类型，配合自定义函数实现解析与验证。但这种方式复杂度较高，需要编写PL/pgSQL或C语言扩展，非必要场景不推荐使用。

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