PostgreSQL中SUBMISSION NUMBER列两种插入场景的最优实现方案

为什么不选序列？ #

序列的自增逻辑独立于表内数据，要是手动插入一个比序列当前值更大的SUBMISSION NUMBER，后续序列生成的值大概率会和已有数据重复，导致插入失败。而且序列无法自动感知表中该列的最大值，没法满足“基于最后可用值自增1”的核心需求，所以序列不是合适的选择。

具体实现步骤 #

1. 创建触发器函数 #

这个函数会在插入数据时自动判断：如果没传入SUBMISSION NUMBER的值，就取表中该列的最大值加1；如果传入了值，直接使用传入值。

CREATE OR REPLACE FUNCTION set_submission_number()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW."SUBMISSION NUMBER" IS NULL THEN
        SELECT COALESCE(MAX("SUBMISSION NUMBER"), 0) + 1
        INTO NEW."SUBMISSION NUMBER"
        FROM your_table_name; -- 替换成你的实际表名
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. 创建绑定触发器 #

把上述函数绑定到表的BEFORE INSERT事件上，确保插入数据前自动处理SUBMISSION NUMBER字段：

CREATE TRIGGER trigger_set_submission_number
BEFORE INSERT ON your_table_name -- 替换成你的实际表名
FOR EACH ROW
EXECUTE FUNCTION set_submission_number();

关键注意事项 #

• 高并发场景下，若多个会话同时插入且都未传入SUBMISSION NUMBER，可能出现竞争条件导致编号重复。如果要处理这种情况，可以在函数中添加LOCK TABLE your_table_name IN EXCLUSIVE MODE;锁定表，但会牺牲部分并发性能。
• 建议给SUBMISSION NUMBER添加唯一约束，避免手动插入时出现重复数据：

ALTER TABLE your_table_name ADD CONSTRAINT unique_submission_number UNIQUE ("SUBMISSION NUMBER");

• 表为空时，COALESCE会默认从1开始生成编号，避免空值异常。

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