postgresql - 在 ON CONFLICT 子句中使用多个 conflict_target

Question

我在表 col1 和 col2 中有两列，它们都是唯一索引的(col1 是唯一的，col2 也是唯一的)。

我需要插入到这个表中，使用 ON CONFLICT 语法并更新其他列，但我不能在 conflict_target 子句中同时使用这两个列。

有效:

INSERT INTO table
...
ON CONFLICT ( col1 ) 
DO UPDATE 
SET 
-- update needed columns here

但是如何对多个列执行此操作，如下所示:

...
ON CONFLICT ( col1, col2 )
DO UPDATE 
SET 
....

Answer 1

ON CONFLICT 需要唯一索引* 来进行冲突检测。所以你只需要在两列上创建一个唯一索引:

t=# create table t (id integer, a text, b text);
CREATE TABLE
t=# create unique index idx_t_id_a on t (id, a);
CREATE INDEX
t=# insert into t values (1, 'a', 'foo');
INSERT 0 1
t=# insert into t values (1, 'a', 'bar') on conflict (id, a) do update set b = 'bar';
INSERT 0 1
t=# select * from t;
 id | a |  b  
----+---+-----
  1 | a | bar

* 除了唯一索引，还可以使用exclusion constraints .这些比唯一约束更通用。假设您的表有 id 和 valid_time 列(valid_time 是一个 tsrange)，并且您希望允许重复的 id，但不是重叠的时间段。唯一约束对您没有帮助，但是有了排除约束，您可以说“如果新记录的 id 等于旧的 id 并且它们的 valid_time 与其 valid_time 重叠。”