postgresql - 如何防止在 postgres 中使用插入返回重复插入？

Question

我正在尝试为我的数据库创建一些规则以防止重复插入，但由于我们也处于 hibernate 状态，因此 .save() 被称为 insert xxx RETURNING *

我似乎无法使插入规则与插入返回一起发挥作用。

CREATE TABLE test ( aaa int, bbb int);

insert into test VALUES(1, 2);

CREATE RULE "testRule1" AS ON INSERT TO  test
  WHERE EXISTS(SELECT 1 FROM test 
                WHERE (aaa, bbb)=(NEW.aaa, NEW.bbb))
  DO INSTEAD NOTHING;

如果我尝试仅使用 1 个条件规则插入

insert into test VALUES(1, 2);
ERROR:  cannot perform INSERT RETURNING on relation "test"
HINT:  You need an unconditional ON INSERT DO INSTEAD rule with a RETURNING clause.

CREATE RULE "testRule2" AS ON INSERT TO  test
  DO INSTEAD INSERT INTO test VALUES(new.*)
  RETURNING test.*;

如果我尝试同时使用有条件和无条件规则插入

insert into test VALUES(1, 2);
ERROR:  infinite recursion detected in rules for relation "test"

这是不可能通过我的设置实现的吗？

Answer 1

避免插入重复项的正确方法是将字段定义为UNIQUE(或PRIMARY KEY)

CREATE TABLE Test (
aaa integer,
bbb integer,
val text, /* The reason why I added this field is explained later */
UNIQUE (aaa,bbb)
)

如果元组已经存在，插入将失败并显示错误代码(从数据库接收错误代码是一件好事，正如我最近解释的 here )。

如果出现错误是 Not Acceptable ，或者如果您想在单个 insert 中插入多条记录而不必关心哪条记录违反了 UNIQUE 约束，则正确的语法是使用 ON CONFLICT 子句。

INSERT INTO Test values (1,1,'Some value')
ON CONFLICT DO NOTHING

基本上，具有唯一元组的第一条记录将被插入。即使查询本身尝试插入重复项(如下例)也是兼容的:

INSERT INTO Test values (2,2,'Will be inserted'), (2,2,'Will not be inserted')
ON CONFLICT DO NOTHING

当然，这也允许您在同一查询中使用 RETURNING 子句。