PostgreSQL中ON CONFLICT能否与JSON对象结合实现条件插入？

该需求完全可以实现，以下是两种符合PostgreSQL语法规范的实现方式：

方案一：使用 INSERT ... SELECT 语法（无需额外约束） #

这是最贴合你原始预期逻辑的写法，不需要依赖表的唯一约束，直接将VALUES替换为SELECT即可追加WHERE条件：

INSERT INTO {TABLE_NAME} (user_id, data)
SELECT '{user_id}', '{data}'
WHERE NOT EXISTS (
    SELECT 1 FROM files 
    WHERE user_id = '{user_id}' 
      -- 注意：->返回JSON类型无法直接匹配字符串，用->>提取为文本类型
      AND data->>'userType' = 'Type1'
);

这个写法逻辑和你最初的预期完全一致，可直接执行。

方案二：使用ON CONFLICT特性（支持JSON字段） #

如果你需要固定基于user_id + data中的userType做去重插入，这种方案并发性能更好，也更安全，只需要先建一个唯一表达式索引即可：

1. 先创建对应唯一索引：

-- 基于user_id和JSON中提取的userType创建唯一索引，data为jsonb类型时性能最优
CREATE UNIQUE INDEX idx_unique_userid_usertype ON {TABLE_NAME} (user_id, (data->>'userType'));

2. 插入语句写法：

INSERT INTO {TABLE_NAME} (user_id, data)
VALUES ('{user_id}', '{data}')
-- 冲突条件和索引定义的表达式保持一致即可
ON CONFLICT (user_id, (data->>'userType')) DO NOTHING;

两种方案选型建议 #

• 单次执行条件插入、或去重逻辑不固定：选方案一，无需修改表结构加索引
• 频繁执行该类去重插入、去重规则固定：选方案二，并发场景下不会出现竞争插入的异常，性能更优

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