PostgreSQL插入后检测是否存在ID反转的重复记录问题

需求 #

表table1含字段id1 uuid、id2 uuid、value text，需实现：插入新记录后，检查表中是否已存在**ID反转（即已有记录的id1=新记录id2，已有记录的id2=新记录id1）且value='some_value'**的记录，若存在则向table2插入对应数据。

原代码问题 #

1. 触发器表名错误：原触发器中表名"table1 "包含多余空格，导致触发器未绑定到正确的table1表。
2. 冗余游标使用：通过游标遍历完全没必要，仅需判断是否存在符合条件的记录即可，游标逻辑冗余且易出错。
3. 未排除新记录：AFTER INSERT触发器执行时，新记录已写入table1，原查询会匹配到自身，导致误判。

修正方案 #

1. 修复触发器绑定 #

先删除无效触发器，重新绑定到正确表：

DROP TRIGGER IF EXISTS chk_insert_new_pair ON "table1 ";
CREATE TRIGGER chk_insert_new_pair
AFTER INSERT
ON table1
FOR EACH ROW
EXECUTE PROCEDURE func_chk_new_pair();

2. 重构触发器函数 #

用EXISTS替代游标，简化逻辑并避免误判：

CREATE OR REPLACE FUNCTION func_chk_new_pair() 
RETURNS trigger AS $$
BEGIN
  -- 日志验证（可保留用于调试）
  INSERT INTO public.temp_log (log_text) 
  VALUES ('id1 : ' || NEW.id1 || ' - id2: ' || NEW.id2);

  -- 检测是否存在符合条件的反转记录（排除自身）
  IF EXISTS (
    SELECT 1 FROM table1 
    WHERE id1 = NEW.id2 
      AND id2 = NEW.id1 
      AND value = 'some_value'
      AND (id1, id2) != (NEW.id1, NEW.id2)
  ) THEN
    INSERT INTO table2 (id1, value) 
    VALUES (NEW.id1, NEW.value);
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

3. 可选：强制阻止重复（部分唯一索引） #

若需彻底禁止此类反转重复记录插入，可创建部分唯一索引，比触发器更高效：

-- 仅当value为'some_value'时，确保(id1,id2)的无序组合唯一
CREATE UNIQUE INDEX idx_unique_reversed_pair ON table1 
(LEAST(id1, id2), GREATEST(id1, id2)) 
WHERE value = 'some_value';

插入反转记录时会直接触发唯一约束错误，无需触发器干预。

内容的提问来源于stack exchange，提问作者mendesbr