为什么此规则不会阻止重复的键违例？ [英] Why doesn't this rule prevent duplicate key violations?

问题描述

（postgresql）我试图将 COPY csv数据放入表中，但我收到重复的键违规错误，没有办法告诉 COPY 忽略这些，所以下面的互联网智慧我试着添加这条规则：

(postgresql) I was trying to COPY csv data into a table but I was getting duplicate key violation errors, and there's no way to tell COPY to ignore those, so following internet wisdom I tried adding this rule:

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;

来解决这个问题，但我还是得到这些错误 - 任何想法为什么？

to circumvent the problem, but I still get those errors - any ideas why ?

推荐答案

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

大致来说，规则会导致在给定命令执行时执行其他命令

Roughly speaking, a rule causes additional commands to be executed when a given command on a given table is executed.

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

But an INSTEAD rule allows you to replace the action:

或者，INSTEAD规则可以用另一个命令替换给定的命令，或者使命令不被执行。

Alternatively, an INSTEAD rule can replace a given command by another, or cause a command not to be executed at all.

因此，我想您希望指定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，什么都不做，AFAIK， DO INSTEAD NOTHING 会这样做。

Without the INSTEAD, your rule is essentially saying "do the INSERT and then do nothing" when you want to say "instead of the INSERT, do nothing" and, AFAIK, the DO INSTEAD NOTHING will do that.

我不是PostgreSQL规则的专家，但我认为添加INSTEAD应该工作。

I'm not an expert on PostgreSQL rules but I think adding the "INSTEAD" should work.

UPDATE ：感谢araqnid 我们知道：

UPDATE: Thanks to araqnid we know that:

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

COPY FROM will invoke any triggers and check constraints on the destination table. However, it will not invoke rules

因此，一个规则在这种情况下不起作用。但是，在COPY FROM期间触发触发器，因此您可以编写一个先前插入的触发器将返回NULL < a>当它检测到重复的行时：

So a rule isn't going to work in this situation. However, triggers are fired during COPY FROM so you could write a BEFORE INSERT trigger that would return NULL when it detected duplicate rows:

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

It can return NULL to skip the operation for the current row. This instructs the executor to not perform the row-level operation that invoked the trigger (the insertion or modification of a particular table row).

也就是说，我认为你会更好地与araqnid的加载到一个临时表，清理它，并将其复制到最终目的地将是一个更明智的解决方案，像你有一个批量加载操作。

That said, I think you'd be better off with araqnid's "load it all into a temporary table, clean it up, and copy it to the final destination" would be a more sensible solution for a bulk loading operation like you have.

这篇关于为什么此规则不会阻止重复的键违例？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！