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

Question

（postgresql）我试图将 csv 数据复制到表中，但遇到重复的密钥违规错误，并且无法告诉 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;

来规避问题，但我仍然遇到这些错误 - 有什么想法吗？

Answer 1

默认规则将内容添加到当前操作：

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

但是 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 ，当您想说“而不是插入，什么都不做”时，您的规则本质上是说“执行插入，然后什么也不做”，并且据我所知，DO INSTEAD NOTHING会这样做。

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

更新：感谢 araqnid 我们知道：

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

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

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

也就是说，我认为你最好使用 araqnid 的“将其全部加载到临时表中，清理它，并将其复制到最终目的地”对于像你这样的批量加载操作来说​​是一个更明智的解决方案。

Answer 2

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;