如何确保数据库同ID连续插入无重复id/value对？

需要确保向表中插入数据时，同一id的连续插入操作不会生成重复的id/value对，目的是跟踪每个id的value随时间的变化情况，同时带删除线的历史行不纳入判断逻辑。

原始有效数据示例 #

现有实现方案 #

触发器方案（当前使用） #

目前通过触发器实现了目标逻辑，但代码较为繁琐：

CREATE OR REPLACE FUNCTION prevent_duplicate_rows()
RETURNS TRIGGER AS $$
DECLARE
    latest_partition_time timestamp;
    latest_partition_value my_table.value%TYPE;
BEGIN
    SELECT MAX(time) INTO latest_partition_time
    FROM my_table
    WHERE NEW.id = id;
    IF latest_partition_time IS NULL THEN
        RETURN NEW;
    ELSE
        SELECT value INTO latest_partition_value
        FROM my_table
        WHERE time = latest_partition_time
        AND NEW.id = id;

        IF NEW.value <> latest_partition_value THEN
            RETURN NEW;
        ELSE
            RETURN NULL;
        END IF;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_duplicate_rows
BEFORE INSERT ON my_table
FOR EACH ROW
EXECUTE FUNCTION prevent_duplicate_rows();

尝试的Check约束方案 #

曾参考方案尝试用自定义函数的Check约束，但无法简便处理违规场景：

CREATE OR REPLACE FUNCTION check_new_value(_id int, _value int)
  RETURNS BOOLEAN AS
$$

  SELECT value != last_value(value) 
                            OVER(PARTITION BY id
                                      ORDER BY time) 
    FROM my_table
      WHERE id = _id;
                        
                        
$$ language sql;

ALTER TABLE my_table ADD CONSTRAINT new_value_ck
CHECK(check_new_value(id, value));

更优实现方案 #

可以简化触发器逻辑，通过一次查询获取最新有效value，避免冗余操作：

CREATE OR REPLACE FUNCTION prevent_duplicate_rows()
RETURNS TRIGGER AS $$
DECLARE
    latest_value my_table.value%TYPE;
BEGIN
    -- 获取当前id的最新有效value（如果带删除线行是逻辑删除，需添加is_deleted = false过滤；物理删除则去掉该条件）
    SELECT value INTO latest_value
    FROM my_table
    WHERE id = NEW.id
      AND is_deleted = false
    ORDER BY time DESC
    LIMIT 1;

    -- 无历史数据或新value与最新值不同时，允许插入；否则丢弃本次插入
    IF latest_value IS NULL OR NEW.value <> latest_value THEN
        RETURN NEW;
    ELSE
        RETURN NULL;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_duplicate_rows
BEFORE INSERT ON my_table
FOR EACH ROW
EXECUTE FUNCTION prevent_duplicate_rows();

方案说明 #

1. 简化查询逻辑：通过ORDER BY time DESC LIMIT 1直接获取目标id的最新value，避免两次查询（先查最大时间再查对应值），提升执行效率。
2. 逻辑更清晰：直接对比新value与最新有效value，相同则返回NULL丢弃插入，否则允许插入。
3. 适配逻辑删除：如果带删除线的行是通过is_deleted字段标记的逻辑删除，可通过过滤条件排除无效行，确保判断基于有效历史数据。

另外，Check约束方案不可行的原因是：PostgreSQL不允许Check约束使用不稳定函数（窗口函数属于此类），且约束无法动态感知表的实时状态，触发器是更可靠的实现方式。

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