如何使用PostgreSQL模拟“插入忽略”和“重复键更新”（SQL合并）？

某些SQL Server具有这样的功能：如果INSERT违反主键/唯一键约束，则会跳过它。例如，MySQL具有INSERT IGNORE。

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

194969 次浏览
小开

PostgreSQL似乎支持名为规则的架构对象。

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

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

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

小开
最佳答案

尝试进行更新。如果它没有修改任何行，这意味着它不存在，那么执行插入。显然，您可以在事务中执行此操作。

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

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

这通常是最简单的方法。你可以用规则来做一些魔术，但它可能会混乱得多。在任何一天，我都会推荐功能包装方法。

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

小开

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

小开

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

基于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";

注：在删除此规则之前，此规则适用于所有INSERT工序，因此并不完全是临时的。

小开

要获得插入忽略逻辑，您可以执行如下操作。我发现简单地从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)
)
小开 
INSERT INTO mytable(col1,col2)
SELECT 'val1','val2'
WHERE NOT EXISTS (SELECT 1 FROM mytable WHERE col1='val1')
小开

对于数据导入脚本，要替换“如果不存在”，在某种程度上，有一个略显笨拙的公式，但仍然有效：

DO
$do$
BEGIN
PERFORM id
FROM whatever_table;


IF NOT FOUND THEN
-- INSERT stuff
END IF;
END
$do$;
小开

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

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;

但是它有一个性能缺陷（见PostgreSQL.org）：

包含EXCEPTION子句

的块的开销要大得多。 进入和退出，而不是一个没有一个。因此，不要使用 不需要的例外.

小开

在PostgreSQL 9.5中，这现在是本机功能（就像MySQL有过几年一样）：

插入..冲突时不执行任何操作/更新（“更新插入”）

9.5支持“ upsert ”操作。 扩展INSERT以接受ON CONFLICT DO UPDATE/IGNORE子句。本条款规定了在可能发生重复违规的情况下应采取的替代措施。

...

新语法的其他示例：

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

对于那些拥有Postgres 9.5或更高版本的人来说，新的在冲突中什么都不做语法应该可以工作：

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;

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

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;
小开

正如@HanMari在他的评论中提到的。当插入到Postgres表中时，On conflict（.）do nothing是不插入重复数据的最佳代码。

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

冲突代码行将允许INSERT语句仍然插入数据行。查询和值代码是将Excel中的日期插入到Postgres数据库表中的示例。 我在Postgres表中添加了约束，以确保ID字段是唯一的。我没有对相同的数据行运行删除操作，而是添加了一行SQL代码，将ID列从1开始重新编号。 例如：

q = 'ALTER id_column serial RESTART WITH 1'

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


提交答案