如何使用触发器来防止PostgreSQL中的重复记录？
我希望创建一个存储过程（在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.
我以前曾经在记录的字段中使用过独特的索引，但是想知道如何编写触发器来完成这个任务。
我以前曾经在记录的字段中使用过独特的索引，但是想知道如何编写触发器来完成这个任务。
这是一个误解。 如果一组列应该是唯一的，那么在任何情况下都应使用UNIQUE约束（或使其成为PK）。 并且要注意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违规。 为了避免大部分时间出现异常1，您可以使用简单的触发器：
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在检查一行是否已经存在并实际插入行之间，在时间片中存在固有的竞争条件，除非您专门锁定表（非常昂贵），否则这是无法避免的。 细节取决于约束的确切定义（可能是可推迟的）。 所以如果一个并发事务同时发现（几乎在同一时刻） (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封装在plpgsql函数中，并捕获重复的密钥违规。 例：
上一篇: How to use triggers to prevent duplicate records in PostgreSQL?
下一篇: Postgresql, update if row with some unique value exists, else insert