gpt4 book ai didi

postgresql - 如何使用触发器来防止 PostgreSQL 中的重复记录?

转载 作者:行者123 更新时间:2023-11-29 11:31:06 27 4
gpt4 key购买 nike

我希望创建一个存储过程(在 plpgsql、PostgreSQL 9.1 中),它首先检查以确保将要插入的记录在它的四个列中是唯一的,或者如果记录被更新,它是更新为唯一值。

  Example:
Record (1,2,3,4) is to be inserted.
If Record (1,2,3,4) already exists, then do not insert a duplicate record.
if Record (1,2,3,4) does not exist, then insert it.

Record (1,2,3,4) is to be updated to (5,6,7,8).
If Record (5,6,7,8) already exists, then do not update the record. (duplicate record not allowed).
If Record (5,6,7,8) does not exist, then update the record to the new values.

我以前曾在记录的字段上使用过唯一索引,但想了解如何编写触发器来实现此目的。

最佳答案

I previously had used a unique index on the record's fields, but wouldlike to learn how a trigger is written to accomplish this.

这是误会。如果一组列应该是唯一的,请使用 UNIQUE 约束(或使其成为 PK)在任何情况下。并注意 NULL 值的特殊作用:

Postgres 9.5 或更高版本

INSERT ... ON CONFLICT ... DO NOTHING 现在有一个更简单的解决方案 - 新“UPSERT”的子集:

INSERT INTO tbl (col1, col2, col3, col4)
VALUES (1, 2, 3, 4)
ON CONFLICT ON CONSTRAINT my_4_col_uni DO NOTHING;

答案的其余部分基本上已经过时了。

Postgres 9.4 或更早版本

触发器可以帮助强制执行约束。但由于固有的竞争条件,它们无法自行强制执行唯一性。

您可以只让唯一约束处理重复键。您将因违规而获得 EXCEPTION。为避免异常大部分时间1,您可以使用简单触发器:

CREATE OR REPLACE FUNCTION tbl_ins_up_before()
RETURNS trigger
LANGUAGE plpgsql AS
$func$
BEGIN
IF EXISTS (SELECT FROM tbl
WHERE (col1, col2, col3, col4)
= (NEW.col1, NEW.col2, NEW.col3, NEW.col4)) THEN
RETURN NULL;
END IF;

RETURN NEW;
END
$func$;

CREATE TRIGGER ins_up_before
BEFORE INSERT OR UPDATE OF col1, col2, col3, col4 -- fire only when relevant
ON tbl
FOR EACH ROW EXECUTE PROCEDURE tbl_ins_up_before();

1 在检查一行是否已经存在和实际插入该行之间的时间片中存在固有的竞争条件,除非您独占锁定表(非常 昂贵)。详细信息取决于您的约束的确切定义(可能是可延迟的)。因此,如果并发事务也发现(几乎在同一时刻)(1,2,3,4) 还不存在并插入,您可能仍会遇到异常在你之前。或者操作可能会中止,但现有行在您提交之前已被删除。

这也不能通过行级锁定来解决,因为您不能锁定 Postgres 中尚不存在的行(谓词锁定),至少在 9.6 版之前是这样。

需要一个唯一约束,它始终强制执行唯一性。

我会有约束,然后使用这个查询:

INSERT INTO tbl (col1, col2, col3, col4)
SELECT 1, 2, 3, 4
WHERE NOT EXISTS (
SELECT 1 FROM tbl
WHERE (col1, col2, col3, col4) = (1, 2, 3, 4);

类似于 UPDATE

您可以将 INSERT/UPDATE 封装在 PL/pgSQL 函数中并捕获重复键违规。示例:

关于postgresql - 如何使用触发器来防止 PostgreSQL 中的重复记录?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/29927523/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com