PostgreSQL中INSERT ON CONFLICT如何实现同值忽略、异值新增而非更新？

我清楚PostgreSQL的INSERT ON CONFLICT可以实现upsert或无操作，但需要实现以下行为：

• 当插入的行中指定字段（id和data）的组合值与现有行完全相同时，忽略该插入操作；
• 当指定字段组合值存在差异时，不更新现有行，而是插入一条全新的行。

示例场景 #

首先创建测试表：

create table test (id text, data text, created_on date);

执行初始插入：

INSERT INTO public.test (id, data, created_on)
  VALUES ('1', 'foo', '2023-03-27'::date);

场景1：插入相同组合值

尝试插入：

INSERT INTO public.test (id, data, created_on)
  VALUES ('1', 'foo', '2023-03-28'::date);

此时id和data的组合与现有行一致，需忽略该插入，表中数据保持不变。

场景2：插入不同组合值

尝试插入：

INSERT INTO public.test (id, data, created_on)
  VALUES ('1', 'bar', '2023-03-28'::date);

此时data值发生变化，id+data组合为新值，需插入新行而非更新原行。最终执行查询：

select * from test;

得到结果：

id | data | created_on
----+------+------------
 1  | foo  | 2023-03-27
 1  | bar  | 2023-03-28

实现方案 #

核心是利用唯一约束/唯一索引来识别重复的id+data组合，再结合INSERT ON CONFLICT DO NOTHING实现需求：

1. 添加唯一约束（或唯一索引）
   给表添加基于id和data的唯一约束：

ALTER TABLE test ADD CONSTRAINT test_id_data_unique UNIQUE (id, data);

或者创建唯一索引（效果等价）：

CREATE UNIQUE INDEX idx_test_id_data ON test (id, data);

2. 使用带冲突处理的插入语句
   之后所有插入操作都用以下格式：

INSERT INTO test (id, data, created_on)
VALUES ('目标id', '目标data', '目标日期')
ON CONFLICT (id, data) DO NOTHING;

这样就能精准实现需求：

• 只要id+data组合已存在，无论其他字段（比如created_on）是否变化，都不执行任何操作；
• 只要id+data组合是新的，就直接插入新行，原有数据不受影响。

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