PostgreSQL插入行时调整优先级列值：行为术语与实现约束问询

专有术语 #

这种插入新行时自动调整现有相同/更低优先级值的操作，并没有一个绝对统一的行业术语，但通常可以称之为 "插入时的优先级移位调整（priority shift on insertion）"，或者更直白点叫优先级挤压重排。本质上是维护一个有序且唯一的优先级序列时的常规操作。

能否通过模型约束实现？ #

很遗憾，PostgreSQL的内置约束（比如UNIQUE）只能做到阻止重复值插入，没办法自动触发移位调整逻辑。所以你没法单纯靠表约束来实现这个需求，必须手动处理——推荐在数据库层通过触发器或存储过程来做，比应用层处理更能保证数据一致性。

具体实现方案（触发器示例） #

我给你写一个实际可运行的触发器方案，你可以直接参考：

1. 先创建示例表 #

CREATE TABLE tasks (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    priority INTEGER NOT NULL UNIQUE
);

2. 创建触发器函数 #

这个函数会在插入新行前检查目标优先级是否存在，存在就把所有≤该优先级的现有行的priority减1：

CREATE OR REPLACE FUNCTION adjust_priority_on_insert()
RETURNS TRIGGER AS $$
BEGIN
    -- 锁定表避免并发插入冲突
    LOCK TABLE tasks IN EXCLUSIVE MODE;
    
    -- 如果目标优先级已存在，执行移位
    IF EXISTS (SELECT 1 FROM tasks WHERE priority = NEW.priority) THEN
        UPDATE tasks
        SET priority = priority - 1
        WHERE priority <= NEW.priority;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

3. 绑定触发器到表的插入操作 #

CREATE TRIGGER trigger_adjust_priority
BEFORE INSERT ON tasks
FOR EACH ROW EXECUTE FUNCTION adjust_priority_on_insert();

4. 测试效果 #

先插入初始数据：

INSERT INTO tasks (name, priority) VALUES ('写需求文档', 3), ('开发功能', 2), ('测试验证', 1);

此时表中优先级是1、2、3。

再插入一个优先级为2的新任务：

INSERT INTO tasks (name, priority) VALUES ('紧急Bug修复', 2);

查询表数据你会发现：

• 原"测试验证"的priority从1变成0
• 原"开发功能"的priority从2变成1
• 新插入的"紧急Bug修复"priority是2
  所有优先级依然保持唯一，且顺序符合你的需求。

注意事项 #

• 加表锁是为了避免多线程并发插入时出现优先级重复的问题，如果你业务并发量极低，也可以考虑去掉，但不推荐。
• 如果你的优先级不允许负数，可能需要额外处理边界情况（比如插入优先级为1时，移位后会出现0，这时候要根据业务调整逻辑）。

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