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

Question

我想用插页。。请务必更新。。对两列具有唯一约束的表的语法。这可能吗？

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;

错误：冲突时，更新是否需要推理规范或约束名称

这也不起作用：

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

错误：不存在与冲突规范匹配的唯一或排除约束

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

如果违反了其中一个唯一约束，有没有办法进行条件更新？这个问题是如何在博士后的两个专栏中的一个专栏中突出冲突？暗指它，但没有提供语法。

Answer 1

因为冲突目标不能是两个不同的唯一约束，所以您必须使用模拟的Upert并自行处理冲突。

--希望

在mytable（col1，col2，col3）中插入冲突DO更新集col3=EXCLUDED上的值（'A'，'B'，0）。col3-1；

WITH upsert AS (
  UPDATE mytable
  SET col1 = 'A', col2 = 'B', col3 = col3 + 1
  WHERE col1 = 'A' OR col2 = 'B'
  RETURNING *
)
INSERT INTO mytable (col1, col2, col3)
SELECT 'A', 'B', 0
WHERE NOT EXISTS (SELECT * FORM upsert);

此语句将生成包含A或B或两者的行，换句话说，满足col1上的唯一性和col2上的唯一性。

不幸的是，这个解决方案受到限制，即A和B之间必须有一些逻辑链接，否则如果插入（'A'， null），然后是（null， B），然后是（A， B），您将最终得到两行，都增加了第三个插入：

| col1 | col2 | col3 |
+------+------+------+
|    A | null |    1 |
| null |    B |    1 |

Answer 2

当我们要求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 CONSTRAINTux_col1_col2。

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

这个想法是保持一个计数器列是最新的，它在唯一列上匹配，或者如果两者都不存在，则插入零...

这条路和你现在走的不一样。“在任一唯一列上匹配”允许在两个或两个列上匹配。如果我理解你的意图，只需要一个标签，并增加适用记录上的计数器。所以：

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;