postgresql MERGE 语法用于对重复的 UPDATE 进行 UPSERT 或 INSERT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4555966/
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
MERGE syntax used to UPSERT or INSERT on duplicate UPDATE
提问by Phill Pafford
So I'm coming from MySQL where I could do INSERT on DUPLICATE UPDATE:
所以我来自 MySQL，在那里我可以在 DUPLICATE UPDATE 上做INSERT：
INSERT INTO table (a,b,c)
VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;
But now I'm using PostgreSQL and there are efforts to add the UPSERT functionality, looks like MERGE might work for what I would like but wanted to see if this is the most optimal syntax. Example Syntax 1, I've also seen this but don't understand how to implement. I haven't tried this yet because I thought MERGE was used for merging data from table1 to Table2 or would something like this work?
但是现在我正在使用 PostgreSQL 并且正在努力添加 UPSERT 功能，看起来 MERGE 可能适用于我想要的但想看看这是否是最佳语法。Example Syntax 1，我也看过这个，但不明白如何实现. 我还没有尝试过这个，因为我认为 MERGE 用于将数据从 table1 合并到 Table2 或者像这样的工作？
MERGE
INTO table
USING table
ON c = 1
WHEN MATCHED THEN
UPDATE
SET c=c+1
WHEN NOT MATCHED THEN
INSERT (a,b,c)
VALUES (1,2,3)
Any other suggestions?
还有其他建议吗？
回答by Michael Buen
Until MERGE is available, use this robust approach: Insert, on duplicate update in PostgreSQL?
在 MERGE 可用之前，请使用这种强大的方法：插入，在 PostgreSQL 中重复更新？
回答by nate c
Until merge is supported the simplest way IMO is to just break it up into two queries:
在支持合并之前，IMO 最简单的方法是将其分解为两个查询：
BEGIN;
INSERT INTO t (a,b,c) VALUES (1,2,3) WHERE id != 1;
UPDATE t SET c=c+1 WHERE id = 1;
END;
where id would be changed to the appropriate condition.
其中 id 将更改为适当的条件。
回答by Peter Eisentraut
MERGE INTO table
USING (VALUES (1, 2, 3)) AS newvalues (a, b, c)
ON table.c = newvalues.c -- or whatever the PK is
WHEN MATCHED THEN UPDATE SET c = c + 1
WHEN NOT MATCHED THEN INSERT (a, b, c)
VALUES (newvalues.a, newvalues.b, newvalues.c)
The key here is that instead of merging in another table you create a constant table source using the VALUESconstruct in the USINGclause. The exact merging rules you can obviously tailor to taste.
这里的关键是使用子句中的VALUES构造创建一个常量表源，而不是在另一个表中合并USING。您显然可以根据口味定制确切的合并规则。
See also http://petereisentraut.blogspot.com/2010/05/merge-syntax.html.
另见http://petereisentraut.blogspot.com/2010/05/merge-syntax.html。
回答by DrColossos
I think "MERGE" is not yet in Postgres but is suposed to be in 9.1.
我认为“MERGE”还没有出现在 Postgres 中，但应该在 9.1 中。
I like to use RULEs instead
我喜欢用RULE代替
CREATE OR REPLACE RULE "insert_ignore"
AS ON INSERT TO "table" WHERE
NEW.id = OLD.id --whatever your conditions are
DO INSTEAD NOTHING;
What you have linked to ("Insert, on duplicate update (postgresql)") is basically some pgsql that you feed the data. I think the RULE is more elegant since you don't need to call them explicitly and they work transparent in the background without the need to call a procedure within your actual INSERT.
您链接到的内容（“插入，重复更新（postgresql）”）基本上是您提供数据的一些 pgsql。我认为 RULE 更优雅，因为您不需要显式调用它们，并且它们在后台透明工作，而无需在实际 INSERT 中调用过程。
