postgresql - 具有多个唯一约束的 Postgres 冲突处理

Question

我想将 insert..on confict do update.. 语法与对两列具有唯一约束的表一起使用。这可能吗？

例如mytable 对 col1 和 col2 有单独的唯一约束。

我会写:

INSERT INTO mytable(col1, col2, col3) values ('A', 'B', 0) ON CONFLICT DO NOTHING;

但是这不起作用:

INSERT INTO mytable(col1, col2, col3) VALUES ('A', 'B', 0) 
ON CONFLICT 
DO UPDATE SET col3 = EXCLUDED.col3 + 1;

错误:ON CONFLICT DO UPDATE 需要推理规范或约束名称

这也行不通:

INSERT INTO mytable(col1, col2, col3) VALUES ('A', 'B', 0)
ON CONFLICT (col1, col2) 
DO UPDATE SET col3 = EXCLUDED.col3 + 1;

错误:没有与 ON CONFLICT 规范匹配的唯一或排除约束

此语法似乎是为两列的单个复合唯一约束而不是两个约束而设计的。

如果违反了任一唯一约束，是否有任何方法可以进行条件更新？这个问题How to upsert in Postgres on conflict on one of 2 columns?暗示它但不提供语法。

Answer 1

当我们要求 DO UPDATE 时，ON CONFLICT 子句需要一个唯一约束。定义主键时，只引用列名即可；这是人们倾向于找到的主要示例。

您提到您“对 col1 和 col2 有单独的唯一约束”，所以我可能会假设您的表定义与此类似:

CREATE TABLE mytable(       
    col1 varchar UNIQUE,    
    col2 varchar UNIQUE,    
    col3 int
);

但是您的查询引用了复合约束；而不是单独的约束。修改后的表定义如下:

CREATE TABLE mytable2(  
    col1 varchar UNIQUE,
    col2 varchar UNIQUE,
    col3 int,
    CONSTRAINT ux_col1_col2 UNIQUE (col1,col2)
);

适用于您上面的查询:

INSERT INTO mytable(col1, col2, col3) VALUES ('A', 'B', 0)
ON CONFLICT (col1, col2) 
DO UPDATE SET col3 = EXCLUDED.col3 + 1;

您可以将此唯一约束引用为 ON CONFLICT (col1, col2) 或 ON CONFLICT ON CONSTRAINT ux_col1_col2。

但是等等，还有更多......

The idea is to keep a counter column up to date which matches on either unique column, or insert zero if neither exists...

这与您在此处采用的路径不同。 “在任一唯一列上匹配”允许在两个列上进行匹配，或者在其中一个或两个都不匹配。如果我理解你的意图，只需一个标签并增加适用记录上的计数器。所以:

CREATE TABLE mytable2(  
    col1 varchar PRIMARY KEY,
    col3 int
);
INSERT INTO mytable2(col1,col3)
SELECT incr_label,0
FROM (VALUES ('A'),('B'),('C')) as increment_list(incr_label)
ON CONFLICT (col1)
DO UPDATE SET col3 = mytable2.col3 + 1
RETURNING col1,col3;