PostgreSQL无主键时基于两列组合的Upsert实现问询

嘿，这个场景我之前处理过，其实完全不用纠结有没有主键——PostgreSQL的INSERT ... ON CONFLICT语法刚好能搞定这种**组合唯一键（无主键）**的Upsert需求，核心是利用你已经创建的组合唯一索引来触发冲突检测。

第一步：确认你的组合索引是「唯一索引」 #

你提到已经创建了组合索引，但要注意：只有唯一组合索引才能被ON CONFLICT用来检测冲突。如果之前建的是普通组合索引，先删掉重建为唯一索引：

-- 先删除原有普通索引（如果有的话）
DROP INDEX IF EXISTS idx_your_table_col1_col2;
-- 创建唯一组合索引
CREATE UNIQUE INDEX idx_your_table_col1_col2 ON your_table (col1, col2);

第二步：编写Upsert语句 #

有了唯一组合索引后，直接用ON CONFLICT (col1, col2)来指定冲突检测的键，然后在DO UPDATE里定义更新逻辑即可。

单条数据Upsert示例

INSERT INTO your_table (col1, col2, col3, col4, update_time)
VALUES ('foo', 'bar', 'new_val3', 'new_val4', NOW())
ON CONFLICT (col1, col2) DO UPDATE
SET 
  col3 = EXCLUDED.col3,  -- EXCLUDED代表原本要插入的那条记录的字段值
  col4 = EXCLUDED.col4,
  update_time = NOW();

这里的EXCLUDED是PostgreSQL提供的特殊临时表，存储了本次INSERT操作中原本要插入的所有字段值，用它来更新字段非常简洁，尤其是批量操作时。

批量数据Upsert示例

如果要批量插入/更新，同样适用，只要把VALUES换成多行数据或者SELECT语句就行：

INSERT INTO your_table (col1, col2, col3)
VALUES 
  ('foo', 'bar', 100),
  ('baz', 'qux', 200),
  ('foo', 'bar', 150)  -- 这条会触发更新逻辑
ON CONFLICT (col1, col2) DO UPDATE
SET col3 = EXCLUDED.col3;

关键注意点 #

• 不需要主键：ON CONFLICT只依赖唯一约束/唯一索引，和主键无关，你的场景完全符合要求。
• 冲突检测的列必须和唯一索引的列完全匹配：比如你的唯一索引是(col1, col2)，ON CONFLICT里就必须写(col1, col2)，不能少列或者换顺序。
• 如果需要自定义更新逻辑，也可以不用EXCLUDED，直接写固定值或者表达式，比如col3 = your_table.col3 + EXCLUDED.col3实现累加更新。

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