为什么这个规则不能防止重复键违规？

Question

为什么这个规则不能防止重复键违规？

8

(postgresql) 我试图将csv数据 COPY 到一个表中，但是我一直遇到重复键冲突的错误，而且没有办法告诉 COPY 忽略这些错误。因此，根据互联网的智慧，我尝试添加以下规则：

CREATE OR REPLACE RULE ignore_duplicate_inserts AS
   ON INSERT TO mytable
   WHERE (EXISTS ( SELECT mytable.id
           FROM mytable
          WHERE mytable.id = new.id)) DO NOTHING;

为了解决这个问题，但我仍然遇到了这些错误 - 有任何想法为什么？

- Hendekagon

2个回答

5

COPY FROM命令不会调用规则（http://www.postgresql.org/docs/9.0/interactive/sql-copy.html#AEN58860）。

我的方法是将CSV数据加载到临时表中，然后使用INSERT...SELECT语句将数据复制到目标表中，如果在CSV数据本身中存在重复项，则首先从临时表中删除这些重复项。代码示例：

BEGIN;
CREATE TEMP TABLE stage_data(key_column, data_columns...) ON COMMIT DROP;
\copy stage_data from data.csv with csv header
-- prevent any other updates while we are merging input (omit this if you don't need it)
LOCK target_data IN SHARE ROW EXCLUSIVE MODE;
-- insert into target table
INSERT INTO target_data(key_column, data_columns...)
   SELECT key_column, data_columns...
   FROM stage_data
   WHERE NOT EXISTS (SELECT 1 FROM target_data
                     WHERE target_data.key_column = stage_data.key_column)
END;

- araqnid

“select distinct” 用于插入数据时可能会起到一定的作用，但是目标数据中可能已经存在重复项。如果只需要清理一个数据集，那么这种方法可以使用。 - Alan Corey

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- mu is too short · Accepted Answer

默认情况下，规则添加内容到当前操作：

粗略地说，当在给定表上执行给定命令时，规则会导致执行其他命令。

但是INSTEAD规则允许您替换操作：

或者，INSTEAD规则可以将给定命令替换为另一个命令，或者根本不执行命令。

因此，我认为你想指定INSTEAD：

CREATE OR REPLACE RULE ignore_duplicate_inserts AS
   ON INSERT TO mytable
   WHERE (EXISTS ( SELECT mytable.id
           FROM mytable
          WHERE mytable.id = new.id)) DO INSTEAD NOTHING;

没有INSTEAD，你的规则基本上是在说“执行INSERT然后什么也不做”，而你想要说“不要执行INSERT，什么也不做”，据我所知，DO INSTEAD NOTHING就能做到这一点。

我不是PostgreSQL规则方面的专家，但我认为添加“INSTEAD”应该可以解决问题。

更新：感谢araqnid 我们知道：

COPY FROM将调用目标表上的任何触发器和检查约束。但它不会调用规则。

因此，在这种情况下，规则无法起作用。但是，在COPY FROM期间会触发触发器，因此您可以编写一个BEFORE INSERT 触发器，在检测到重复行时返回NULL：

它可以返回NULL以跳过当前行的操作。这指示执行程序不执行调用触发器的行级操作（特定表行的插入或修改）。

话虽如此，我认为araqnid的“将所有内容加载到临时表中，清理它，然后将其复制到最终目标”对于像您这样的批量加载操作来说是更明智的解决方案。