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

问题描述

某些 SQL 服务器有一个特性，如果 INSERT 违反主键/唯一键约束，则跳过该功能.例如，MySQL 有 INSERT IGNORE.

Some SQL servers have a feature where INSERT is skipped if it would violate a primary/unique key constraint. For instance, MySQL has INSERT IGNORE.

使用 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 合并) 与 postgresql?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！