database 如何使用 postgresql 模拟“插入忽略”和“重复密钥更新”（sql 合并）？

how to emulate "insert ignore" and "on duplicate key update" (sql merge) with postgresql?

提问by gpilotino

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

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

What's the best way to emulate INSERT IGNOREand ON DUPLICATE KEY UPDATEwith PostgreSQL?

模拟INSERT IGNORE和ON DUPLICATE KEY UPDATE使用 PostgreSQL的最佳方法是什么？

采纳答案by Magnus Hagander

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.

如果您不想将额外的代码放在客户端，您当然可以将其包装在一个函数中。您还需要一个循环来处理这种想法中非常罕见的竞争条件。

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.

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

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.

这通常是最简单的方法。你可以用规则做一些魔法，但它可能会变得更加混乱。我建议在任何一天都采用函数内包装方法。

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)

这适用于单行或几行值。如果您正在处理大量行，例如来自子查询的行，您最好将其拆分为两个查询，一个用于 INSERT，一个用于 UPDATE（当然作为适当的连接/子选择 - 无需编写您的主要过滤两次）

回答by warren

With PostgreSQL 9.5, this is now native functionality(like MySQL has hadfor several years):

使用 PostgreSQL 9.5，这现在是本机功能（就像MySQL 已经有好几年了）：

INSERT ... ON CONFLICT DO NOTHING/UPDATE ("UPSERT")

9.5 brings support for "UPSERT" operations. INSERT is extended to accept an ON CONFLICT DO UPDATE/IGNORE clause. This clause specifies an alternative action to take in the event of a would-be duplicate violation.

INSERT ... ON CONFLICT DO NOTHING/UPDATE ("UPSERT")

9.5 带来了对“UPSERT”操作的支持。INSERT 被扩展为接受 ON CONFLICT DO UPDATE/IGNORE 子句。本条款规定了在可能的重复违规事件中采取的替代行动。

...

...

Further example of new syntax:

新语法的进一步示例：

INSERT INTO user_logins (username, logins)
VALUES ('Naomi',1),('James',1) 
ON CONFLICT (username)
DO UPDATE SET logins = user_logins.logins + EXCLUDED.logins;

回答by EoghanM

Edit: in case you missed warren's answer, PG9.5 now has this natively; time to upgrade!

编辑：如果你错过了沃伦的回答，PG9.5 现在有这个；是时候升级了！

---

---

Building on Bill Karwin's answer, to spell out what a rule based approach would look like (transferring from another schema in the same DB, and with a multi-column primary key):

以 Bill Karwin 的回答为基础，阐明基于规则的方法的样子（从同一数据库中的另一个模式转移，并使用多列主键）：

CREATE RULE "my_table_on_duplicate_ignore" AS ON INSERT TO "my_table"
  WHERE EXISTS(SELECT 1 FROM my_table 
                WHERE (pk_col_1, pk_col_2)=(NEW.pk_col_1, NEW.pk_col_2))
  DO INSTEAD NOTHING;
INSERT INTO my_table SELECT * FROM another_schema.my_table WHERE some_cond;
DROP RULE "my_table_on_duplicate_ignore" ON "my_table";

Note: The rule applies to all INSERToperations until the rule is dropped, so not quite ad hoc.

注意：规则适用于所有INSERT操作，直到规则被删除，所以不是特别的。

回答by hanmari

For those of you that have Postgres 9.5 or higher, the new ON CONFLICT DO NOTHINGsyntax should work:

对于那些拥有 Postgres 9.5 或更高版本的人，新的ON CONFLICT DO NOTHING语法应该可以工作：

INSERT INTO target_table (field_one, field_two, field_three ) 
SELECT field_one, field_two, field_three
FROM source_table
ON CONFLICT (field_one) DO NOTHING;

For those of us who have an earlier version, this right join will work instead:

对于我们这些拥有早期版本的人来说，这个正确的联接将起作用：

INSERT INTO target_table (field_one, field_two, field_three )
SELECT source_table.field_one, source_table.field_two, source_table.field_three
FROM source_table 
LEFT JOIN target_table ON source_table.field_one = target_table.field_one
WHERE target_table.field_one IS NULL;

回答by Keyo

To get the insert ignorelogic you can do something like below. I found simply inserting from a select statement of literal values worked best, then you can mask out the duplicate keys with a NOT EXISTS clause. To get the update on duplicate logic I suspect a pl/pgsql loop would be necessary.

要获得插入忽略逻辑，您可以执行以下操作。我发现简单地从文字值的 select 语句中插入效果最好，然后您可以使用 NOT EXISTS 子句屏蔽重复键。为了获得重复逻辑的更新，我怀疑需要一个 pl/pgsql 循环。

INSERT INTO manager.vin_manufacturer
(SELECT * FROM( VALUES
  ('935',' Citro?n Brazil','Citro?n'),
  ('ABC', 'Toyota', 'Toyota'),
  ('ZOM',' OM','OM')
  ) as tmp (vin_manufacturer_id, manufacturer_desc, make_desc)
  WHERE NOT EXISTS (
    --ignore anything that has already been inserted
    SELECT 1 FROM manager.vin_manufacturer m where m.vin_manufacturer_id = tmp.vin_manufacturer_id)
)

回答by user2342158

INSERT INTO mytable(col1,col2) 
    SELECT 'val1','val2' 
    WHERE NOT EXISTS (SELECT 1 FROM mytable WHERE col1='val1')

回答by Bill Karwin

Looks like PostgreSQL supports a schema object called a rule.

看起来 PostgreSQL 支持一个称为rule的模式对象。

http://www.postgresql.org/docs/current/static/rules-update.html

http://www.postgresql.org/docs/current/static/rules-update.html

You could create a rule ON INSERTfor a given table, making it do NOTHINGif a row exists with the given primary key value, or else making it do an UPDATEinstead of the INSERTif a row exists with the given primary key value.

您可以ON INSERT为给定的表创建一个规则，NOTHING如果存在具有给定主键值的行，则执行该规则，或者如果存在具有给定主键值的行UPDATE，INSERT则使其执行而不是。

I haven't tried this myself, so I can't speak from experience or offer an example.

我自己没有尝试过，所以我不能从经验中说话或提供一个例子。

回答by Yankeeownz

As @hanmari mentioned in his comment. when inserting into a postgres tables, the on conflict (..) do nothing is the best code to use for not inserting duplicate data.:

正如@hanmari 在他的评论中提到的。插入 postgres 表时，冲突 (..) do nothing 是用于不插入重复数据的最佳代码。：

query = "INSERT INTO db_table_name(column_name)
         VALUES(%s) ON CONFLICT (column_name) DO NOTHING;"

The ON CONFLICT line of code will allow the insert statement to still insert rows of data. The query and values code is an example of inserted date from a Excel into a postgres db table. I have constraints added to a postgres table I use to make sure the ID field is unique. Instead of running a delete on rows of data that is the same, I add a line of sql code that renumbers the ID column starting at 1. Example:

ON CONFLICT 代码行将允许插入语句仍然插入数据行。查询和值代码是从 Excel 插入日期到 postgres db 表的示例。我在 postgres 表中添加了约束，用于确保 ID 字段是唯一的。我没有在相同的数据行上运行删除，而是添加了一行 sql 代码，从 1 开始对 ID 列重新编号。示例：

q = 'ALTER id_column serial RESTART WITH 1'

If my data has an ID field, I do not use this as the primary ID/serial ID, I create a ID column and I set it to serial. I hope this information is helpful to everyone. *I have no college degree in software development/coding. Everything I know in coding, I study on my own.

如果我的数据有 ID 字段，我不会将其用作主要 ID/序列 ID，而是创建一个 ID 列并将其设置为序列。我希望这些信息对每个人都有帮助。*我没有软件开发/编码方面的大学学位。我在编码方面所知道的一切，都是我自己学习的。

回答by NumberFour

This solution avoids using rules:

此解决方案避免使用规则：

BEGIN
   INSERT INTO tableA (unique_column,c2,c3) VALUES (1,2,3);
EXCEPTION 
   WHEN unique_violation THEN
     UPDATE tableA SET c2 = 2, c3 = 3 WHERE unique_column = 1;
END;

but it has a performance drawback (see PostgreSQL.org):

但它有一个性能缺陷（请参阅PostgreSQL.org）：

A block containing an EXCEPTION clause is significantly more expensive to enter and exit than a block without one. Therefore, don't use EXCEPTION without need.

包含 EXCEPTION 子句的块比没有 EXCEPTION 子句的块进入和退出的成本要高得多。因此，不要在不需要的情况下使用 EXCEPTION。

回答by David Noriega

On bulk, you can always delete the row before the insert. A deletion of a row that doesn't exist doesn't cause an error, so its safely skipped.

批量处理时，您始终可以在插入之前删除该行。删除不存在的行不会导致错误，因此可以安全地跳过。