postgresql 在 ON CONFLICT 子句中使用多个冲突目标
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35888012/
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
Use multiple conflict_target in ON CONFLICT clause
提问by Oto Shavadze
I have two columns in table col1, col2, they both are unique indexed (col1 is unique and so is col2).
我在表中两列col1，col2他们都是独一无二的索引（COL1是唯一的，因此是COL2）。
I need at insert into this table, use ON CONFLICTsyntax and update other columns, but I can't use both column in conflict_targetclause.
我需要插入到这个表中，使用ON CONFLICT语法并更新其他列，但我不能在conflict_target子句中同时使用两列。
It works:
有用：
INSERT INTO table
...
ON CONFLICT ( col1 )
DO UPDATE
SET
-- update needed columns here
But how to do this for several columns, something like this:
但是如何对几列执行此操作，如下所示：
...
ON CONFLICT ( col1, col2 )
DO UPDATE
SET
....
采纳答案by e4c5
A sample table and data
示例表和数据
CREATE TABLE dupes(col1 int primary key, col2 int, col3 text,
CONSTRAINT col2_unique UNIQUE (col2)
);
INSERT INTO dupes values(1,1,'a'),(2,2,'b');
Reproducing the problem
重现问题
INSERT INTO dupes values(3,2,'c')
ON CONFLICT (col1) DO UPDATE SET col3 = 'c', col2 = 2
Let's call this Q1. The result is
我们称之为 Q1。结果是
ERROR: duplicate key value violates unique constraint "col2_unique"
DETAIL: Key (col2)=(2) already exists.
What the documentationsays
文档说明了什么
conflict_target can perform unique index inference. When performing inference, it consists of one or more index_column_name columns and/or index_expression expressions, and an optional index_predicate. All table_name unique indexes that, without regard to order, contain exactly the conflict_target-specified columns/expressions are inferred (chosen) as arbiter indexes. If an index_predicate is specified, it must, as a further requirement for inference, satisfy arbiter indexes.
冲突目标可以执行唯一索引推断。执行推理时，它由一个或多个 index_column_name 列和/或 index_expression 表达式以及一个可选的 index_predicate 组成。所有 table_name 唯一索引，不考虑顺序，完全包含冲突目标指定的列/表达式，都被推断（选择）为仲裁索引。如果指定了 index_predicate，作为推理的进一步要求，它必须满足仲裁索引。
This gives the impression that the following query should work, but it does not because it would actually require a together unique index on col1 and col2. However such an index would not guarantee that col1 and col2 would be unique individually which is one of the OP's requirements.
这给人的印象是下面的查询应该可以工作，但事实并非如此，因为它实际上需要 col1 和 col2 上的唯一索引。然而，这样的索引不能保证 col1 和 col2 单独是唯一的，这是 OP 的要求之一。
INSERT INTO dupes values(3,2,'c')
ON CONFLICT (col1,col2) DO UPDATE SET col3 = 'c', col2 = 2
Let's call this query Q2 (this fails with a syntax error)
让我们将此查询称为 Q2（由于语法错误而失败）
Why?
为什么？
Postgresql behaves this way is because what should happen when a conflict occurs on the second column is not well defined. There are number of possibilities. For example in the above Q1 query, should postgresql update col1when there is a conflict on col2? But what if that leads to another conflict on col1? how is postgresql expected to handle that?
Postgresql 的这种行为是因为在第二列上发生冲突时应该发生的事情没有明确定义。有多种可能性。例如在上面的 Q1 查询中，col1当发生冲突时postgresql 应该更新col2吗？但是，如果这导致了另一场冲突col1呢？postgresql 预计如何处理？
A solution
一个办法
A solution is to combine ON CONFLICT with old fashioned UPSERT.
一个解决方案是将 ON CONFLICT 与老式的 UPSERT结合起来。
CREATE OR REPLACE FUNCTION merge_db(key1 INT, key2 INT, data TEXT) RETURNS VOID AS
$$
BEGIN
LOOP
-- first try to update the key
UPDATE dupes SET col3 = data WHERE col1 = key1 and col2 = key2;
IF found THEN
RETURN;
END IF;
-- not there, so try to insert the key
-- if someone else inserts the same key concurrently, or key2
-- already exists in col2,
-- we could get a unique-key failure
BEGIN
INSERT INTO dupes VALUES (key1, key2, data) ON CONFLICT (col1) DO UPDATE SET col3 = data;
RETURN;
EXCEPTION WHEN unique_violation THEN
BEGIN
INSERT INTO dupes VALUES (key1, key2, data) ON CONFLICT (col2) DO UPDATE SET col3 = data;
RETURN;
EXCEPTION WHEN unique_violation THEN
-- Do nothing, and loop to try the UPDATE again.
END;
END;
END LOOP;
END;
$$
LANGUAGE plpgsql;
You would need to modify the logic of this stored function so that it updates the columns exactly the way you want it to. Invoke it like
您需要修改此存储函数的逻辑，以便它完全按照您希望的方式更新列。像这样调用它
SELECT merge_db(3,2,'c');
SELECT merge_db(1,2,'d');
回答by Paul A Jungwirth
ON CONFLICTrequires a unique index* to do the conflict detection. So you just need to create a unique index on both columns:
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
* In addition to unique indexes, you can also use exclusion constraints. These are a bit more general than unique constraints. Suppose your table had columns for idand valid_time(and valid_timeis a tsrange), and you wanted to allow duplicate ids, but not for overlapping time periods. A unique constraint won't help you, but with an exclusion constraint you can say "exclude new records if their idequals an old idand also their valid_timeoverlaps its valid_time."
* 除了唯一索引，您还可以使用排除约束。这些比唯一约束更通用。假设您的表有id和valid_time（并且valid_time是 a tsrange）的列，并且您希望允许重复的ids，但不允许重叠的时间段。唯一约束对您无济于事，但使用排除约束，您可以说“如果新记录id等于旧记录id，并且它们valid_time与valid_time.重叠，则排除新记录”。
回答by Peter Krauss
In nowadays is (seems) impossible. Neither the last version of the ON CONFLICTsyntaxpermits to repeat the clause, nor with CTEis possible: not is possible to breack the INSERT from ON CONFLICT to add more conflict-targets.
在当今（似乎）是不可能的。ON CONFLICT语法的最后一个版本不允许重复该子句，也不可能使用CTE：不可能从 ON CONFLICT 中断 INSERT 以添加更多冲突目标。
回答by Vladimir Voznesensky
- Create a constraint (foreign index, for example).
- 创建约束（例如，外部索引）。
OR/AND
或/与
- Look at existing constraints (\d in psq).
- Use ON CONSTRAINT(constraint_name) in the INSERT clause.
- 查看现有约束（psq 中的\d）。
- 在 INSERT 子句中使用 ON CONSTRAINT(constraint_name)。
回答by Martin Gerhardy
If you are using postgres 9.5, you can use the EXCLUDED space.
如果您使用的是 postgres 9.5，则可以使用 EXCLUDED 空间。
Example taken from What's new in PostgreSQL 9.5:
INSERT INTO user_logins (username, logins)
VALUES ('Naomi',1),('James',1)
ON CONFLICT (username)
DO UPDATE SET logins = user_logins.logins + EXCLUDED.logins;
回答by Niko Dunk
Kind of hacky but I solved this by concatenating the two values from col1 and col2 into a new column, col3 (kind of like an index of the two) and compared against that. This only works if you need it to match BOTH col1 and col2.
有点hacky，但我通过将 col1 和 col2 中的两个值连接到一个新列 col3 （有点像两者的索引）并与之进行比较来解决这个问题。这仅在您需要它匹配 col1 和 col2 时才有效。
INSERT INTO table
...
ON CONFLICT ( col3 )
DO UPDATE
SET
-- update needed columns here
Where col3 = the concatenation of the values from col1 and col2.
其中 col3 = 来自 col1 和 col2 的值的串联。
回答by user2625834
ON CONFLICT is very clumsy solution, run
ON CONFLICT 是非常笨拙的解决方案，运行
UPDATE dupes SET key1=, key2= where key3=
if rowcount > 0
INSERT dupes (key1, key2, key3) values (,,);
works on Oracle, Postgres and all other database
适用于 Oracle、Postgres 和所有其他数据库
回答by KajMagnus
You can typically (I would think) generate a statement with only one on conflictthat specifies the one and only constraint that is of relevance, for the thing you are inserting.
您通常（我认为）可以生成一个语句，其中仅on conflict指定一个与您插入的内容相关的唯一约束。
Because typically, only one constraint is the "relevant" one, at a time. (If many, then I'm wondering if something is weird / oddly-designed, hmm.)
因为通常，一次只有一个约束是“相关的”约束。（如果很多，那么我想知道是否有什么奇怪的/奇怪的设计，嗯。）
Example:
(License: NotCC0, only CC-By)
示例：（
许可证：不是CC0，只有 CC-By）
// there're these unique constraints:
// unique (site_id, people_id, page_id)
// unique (site_id, people_id, pages_in_whole_site)
// unique (site_id, people_id, pages_in_category_id)
// and only *one* of page-id, category-id, whole-site-true/false
// can be specified. So only one constraint is "active", at a time.
val thingColumnName = thingColumnName(notfificationPreference)
val insertStatement = s"""
insert into page_notf_prefs (
site_id,
people_id,
notf_level,
page_id,
pages_in_whole_site,
pages_in_category_id)
values (?, ?, ?, ?, ?, ?)
-- There can be only one on-conflict clause.
on conflict (site_id, people_id, $thingColumnName) <—— look
do update set
notf_level = excluded.notf_level
"""
val values = List(
siteId.asAnyRef,
notfPref.peopleId.asAnyRef,
notfPref.notfLevel.toInt.asAnyRef,
// Only one of these is non-null:
notfPref.pageId.orNullVarchar,
if (notfPref.wholeSite) true.asAnyRef else NullBoolean,
notfPref.pagesInCategoryId.orNullInt)
runUpdateSingleRow(insertStatement, values)
And:
和：
private def thingColumnName(notfPref: PageNotfPref): String =
if (notfPref.pageId.isDefined)
"page_id"
else if (notfPref.pagesInCategoryId.isDefined)
"pages_in_category_id"
else if (notfPref.wholeSite)
"pages_in_whole_site"
else
die("TyE2ABK057")
The on conflictclause is dynamically generated, depending on what I'm trying to do. If I'm inserting a notification preference, for a page — then there can be a unique conflict, on the site_id, people_id, page_idconstraint. And if I'm configuring notification prefs, for a category — then instead I know that the constraint that can get violated, is site_id, people_id, category_id.
该on conflict子句是动态生成的，具体取决于我要做什么。如果我要为页面插入通知首选项，那么site_id, people_id, page_id约束上可能存在独特的冲突。如果我正在为一个类别配置通知首选项，那么我知道可能会违反的约束是site_id, people_id, category_id.
So I can, and fairly likely you too, in your case?, generate the correct on conflict (... columns ), because I know what I wantto do, and then I know which single one of the many unique constraints, is the one that can get violated.
所以我可以，而且很可能你也可以，在你的情况下？，生成正确的on conflict (... columns )，因为我知道我想要做什么，然后我知道许多唯一约束中的哪一个是可以被违反的。
回答by Jubair
Vlad got the right idea.
弗拉德的想法是正确的。
First you have to create a table unique constraint on the columns col1, col2Then once you do that you can do the following:
首先，您必须在列上创建表唯一约束，col1, col2然后您可以执行以下操作：
INSERT INTO dupes values(3,2,'c')
ON CONFLICT ON CONSTRAINT dupes_pkey
DO UPDATE SET col3 = 'c', col2 = 2
