用于对重复 UPDATE 进行 UPSERT 或 INSERT 的 MERGE 语法

发布于 2024-10-09 18:17:20 字数 799 浏览 3 评论 0原文

所以我来自 MySQL，在那里我可以执行 INSERT关于重复更新：

INSERT INTO table (a,b,c) 
VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;

但现在我正在使用 PostgreSQL，并且正在努力添加 UPSERT 功能，看起来 MERGE 可能适合我想要的功能，但想看看这是否是最佳语法。示例语法 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)

还有其他建议吗？

原文

So I'm coming from MySQL where I could do INSERT on DUPLICATE UPDATE:

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?

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

樱娆 2024-10-16 18:17:20

在 MERGE 可用之前，请使用这种强大的方法：在 PostgreSQL 中进行重复更新时插入？

回复收藏 0 原文

陈独秀 2024-10-16 18:17:20

在支持合并之前，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;

其中 id 将更改为适当的条件。

Until merge is supported the simplest way IMO is to just break it up into two queries:

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.

回复收藏 0 原文

梦过后 2024-10-16 18:17:20

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)

这里的关键是，您不是在另一个表中合并，而是使用 USING 子句中的 VALUES 构造创建一个常量表源。您显然可以根据口味定制确切的合并规则。

另请参阅http://petereisentraut.blogspot.com/2010/05/merge-syntax .html。

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 VALUES construct in the USING clause. The exact merging rules you can obviously tailor to taste.

回复收藏 0 原文

夏の忆 2024-10-16 18:17:20

我认为 Postgres 中还没有“MERGE”，但应该在 9.1 中。

我喜欢使用 RULE 代替

CREATE OR REPLACE RULE "insert_ignore"
AS ON INSERT TO "table" WHERE
  NEW.id = OLD.id --whatever your conditions are
DO INSTEAD NOTHING;

您链接到的内容（“插入，关于重复更新（postgresql）”）基本上是您提供数据的一些 pgsql。我认为 RULE 更优雅，因为您不需要显式调用它们，并且它们在后台透明工作，无需在实际 INSERT 中调用过程。

I think "MERGE" is not yet in Postgres but is suposed to be in 9.1.

I like to use RULEs instead

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.

回复收藏 0 原文

~没有更多了~