首页 文章

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

提问于
浏览 次
3

我希望创建一个存储过程（在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.

我之前在记录的字段上使用过唯一索引，但是想了解如何编写触发器来完成此操作 .

postgresql database-design triggers constraints plpgsql

1 回答

  • 9

    我之前在记录的字段上使用过唯一索引，但是想了解如何编写触发器来完成此操作 .

    这是一种误解 . 如果一组列应该是唯一的，请使用 UNIQUE 约束（或将其设为PK） in any case . 并注意NULL值的特殊作用：

    答案的其余部分基本上已经过时了 . 自 Postgres 9.5 添加 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 违规行为 . 为避免大多数时间的异常，您可以使用简单的触发器：

    CREATE OR REPLACE FUNCTION tbl_ins_up_before()
  RETURNS trigger AS
$func$
BEGIN

IF EXISTS (SELECT 1 FROM tbl
           WHERE (col1,     col2,     col3,     col4)
           = (NEW.col1, NEW.col2, NEW.col3, NEW.col4)) THEN
   RETURN NULL;
END IF;

RETURN NEW;

END
$func$  LANGUAGE plpgsql;

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在检查行是否已存在和实际插入行之间的时间片中存在固有的竞争条件，除非您独占锁定表（非常昂贵），否则无法避免 . 细节取决于约束的确切定义（可以推迟） . 所以你可能 still get an exception 如果并发事务也发现（几乎在同一时刻） (1,2,3,4) 还没有，并在你之前插入 . 或者操作可能会中止，但在您提交之前会删除现有行 .

    这也无法通过行级锁定来解决，因为在Postgres版本9.6中，您无法锁定尚未存在的行（谓词锁定） .

    您 need 是一个独特的约束，它始终强制执行唯一性 .

    我会有约束，然后使用此查询：

    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 .

    您可以在plpgsql函数中封装 INSERT / UPDATE 并捕获重复的密钥违规 . 例：

    回复于

相关问题