postgresql 具有多个唯一约束的 Postgres 冲突处理
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36886134/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres conflict handling with multiple unique constraints
提问by qed-
I would like to use the insert.. on confict do update.. syntax with a table that has unique constraints on two columns. Is this possible?
我想使用 insert.. on confict do update.. 语法与对两列具有唯一约束的表。这可能吗？
e.g. mytable has separate unique constraints on col1 and col2.
例如，mytable 对 col1 和 col2 具有单独的唯一约束。
I can write:
我可以写：
INSERT INTO mytable(col1, col2, col3) values ('A', 'B', 0) ON CONFLICT DO NOTHING;
However this doesn't work:
但是，这不起作用：
INSERT INTO mytable(col1, col2, col3) VALUES ('A', 'B', 0)
ON CONFLICT
DO UPDATE SET col3 = EXCLUDED.col3 + 1;
ERROR: ON CONFLICT DO UPDATE requires inference specification or constraint name
错误：ON CONFLICT DO UPDATE 需要推理规范或约束名称
This also doesn't work:
这也不起作用：
INSERT INTO mytable(col1, col2, col3) VALUES ('A', 'B', 0)
ON CONFLICT (col1, col2)
DO UPDATE SET col3 = EXCLUDED.col3 + 1;
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
错误：没有与 ON CONFLICT 规范匹配的唯一或排除约束
This syntax seems to be designed for a single composite unique constraint over two columns, rather than two constraints.
这种语法似乎是为两列上的单个复合唯一约束而设计的，而不是两个约束。
Is there any way to do a conditional update if either unique constraint is violated? This question How to upsert in Postgres on conflict on one of 2 columns?alludes to it but doesn't provide the syntax.
如果违反了唯一约束，有没有办法进行条件更新？这个问题如何在 Postgres 中插入 2 列之一的冲突？暗示它但不提供语法。
回答by Vic
The ON CONFLICTclause needs a single unique constraint when we ask it to DO UPDATE. When a primary key is defined, it is sufficient to just reference the column name; which is the dominant example one tends to find.
ON CONFLICT当我们要求它时，子句需要一个唯一的约束DO UPDATE。定义主键时，只引用列名就足够了；这是人们倾向于找到的主要例子。
You mention that you have 'separate unique constraints on col1 and col2', so I might assume your table definition is similar to this:
您提到您对 col1 和 col2 有“单独的唯一约束”，因此我可能假设您的表定义与此类似：
CREATE TABLE mytable(
col1 varchar UNIQUE,
col2 varchar UNIQUE,
col3 int
);
But your query is referencing a composite constraint; rather than separate constraints. A modified table definition like this:
但是您的查询引用了复合约束；而不是单独的约束。修改后的表定义如下：
CREATE TABLE mytable2(
col1 varchar UNIQUE,
col2 varchar UNIQUE,
col3 int,
CONSTRAINT ux_col1_col2 UNIQUE (col1,col2)
);
would work with your query above:
将适用于您上面的查询：
INSERT INTO mytable(col1, col2, col3) VALUES ('A', 'B', 0)
ON CONFLICT (col1, col2)
DO UPDATE SET col3 = EXCLUDED.col3 + 1;
You can reference this unique constraint as either ON CONFLICT (col1, col2)or as ON CONFLICT ON CONSTRAINT ux_col1_col2.
您可以将此唯一约束引用为ON CONFLICT (col1, col2)或ON CONFLICT ON CONSTRAINT ux_col1_col2。
But wait, there's more...
但是等等，还有更多……
The idea is to keep a counter column up to date which matches on either unique column, or insert zero if neither exists...
这个想法是保持一个计数器列是最新的，它与任一唯一列匹配，或者如果都不存在则插入零......
That's a different path than you're taking here. "matches on either unique column" allows for matching on both, either, or neither. If I understand your intent, just have a single label and increment the counters on the applicable records. So:
这与你在这里走的路不同。“匹配任一唯一列”允许匹配两者之一，或两者都不匹配。如果我理解您的意图，只需一个标签并增加适用记录上的计数器。所以：
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;
