如何模拟“插入忽略”和“关于重复密钥更新” （sql merge）与postgresql？ [英] how to emulate "insert ignore" and "on duplicate key update" (sql merge) with postgresql?

问题描述

使用postgresql模拟insert ignore和on duplicate key update的最佳方法是什么？

what's the best way to emulate "insert ignore" and "on duplicate key update" with postgresql ?

推荐答案

做一个更新。如果它不修改任何行，意味着它不存在，那么做一个插入。显然，你在一个事务中这样做。

Try to do an UPDATE. If it doesn't modify any row that means it didn't exist, so do an insert. Obviously, you do this inside a transaction.

如果你不想在客户端放置额外的代码，你可以在函数中包装。你也需要一个循环，在这种思维中非常罕见的种族条件。

You can of course wrap this in a function if you don't want to put the extra code on the client side. You also need a loop for the very rare race condition in that thinking.

文档中有一个示例： http://www.postgresql.org/docs/9.3/static/plpgsql-control-structures.html ，示例40-2在底部。

There's an example of this in the documentation: http://www.postgresql.org/docs/9.3/static/plpgsql-control-structures.html, example 40-2 right at the bottom.

这通常是最简单的方法。你可以做一些魔法与规则，但它可能会是一个更麻烦。我建议在任何一天的换行函数方法。

That's usually the easiest way. You can do some magic with rules, but it's likely going to be a lot messier. I'd recommend the wrap-in-function approach over that any day.

这适用于单行或几行的值。如果你处理大量的行，例如从一个子查询，你最好把它分成两个查询，一个用于INSERT和一个UPDATE（当然是一个合适的连接/子查询 - 不需要写你的主过滤器两次）

This works for single row, or few row, values. If you're dealing with large amounts of rows for example from a subquery, you're best of splitting it into two queries, one for INSERT and one for UPDATE (as an appropriate join/subselect of course - no need to write your main filter twice)

这篇关于如何模拟“插入忽略”和“关于重复密钥更新” （sql merge）与postgresql？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！