PostgreSQL：同name与id的多行version字段更新问题及触发器尝试

问题场景 #

当向数据表插入包含相同name和id的记录时，不新增重复行，而是将已有对应记录的version字段值加1；若为新的name&id组合，则插入新行并将version设为初始值（如1）。

原代码存在的问题 #

你编写的触发器函数有以下几个关键问题：

• 硬编码了name='A'和id='1'的匹配条件，无法处理其他name&id组合的重复插入
• 未判断是否存在重复行，每次插入都会执行update操作，无匹配行时做无用功
• 表名错误地写为version，应该替换为你的实际数据表名
• 未处理新记录插入的逻辑，直接返回NEW会导致重复行被插入，不符合需求

方案一：使用触发器实现 #

1. 创建修正后的触发器函数

假设你的数据表名为your_table，字段为name、id、version，请替换为实际表名和字段名：

CREATE OR REPLACE FUNCTION update_version_on_duplicate()
RETURNS TRIGGER 
LANGUAGE PLPGSQL
AS $$
BEGIN
    -- 检查当前插入的name&id是否已存在
    IF EXISTS (SELECT 1 FROM your_table WHERE name = NEW.name AND id = NEW.id) THEN
        -- 存在则更新对应行的version
        UPDATE your_table 
        SET version = version + 1 
        WHERE name = NEW.name AND id = NEW.id;
        -- 返回NULL，阻止插入重复行
        RETURN NULL;
    ELSE
        -- 不存在则设置初始version为1，允许插入新行
        NEW.version = 1;
        RETURN NEW;
    END IF;
END;
$$;

2. 创建触发器

绑定触发器到数据表的INSERT操作前：

CREATE TRIGGER trigger_update_version
BEFORE INSERT ON your_table
FOR EACH ROW
EXECUTE FUNCTION update_version_on_duplicate();

方案二：使用UPSERT（推荐） #

PostgreSQL的INSERT ... ON CONFLICT语法（即UPSERT）可以更简洁地实现需求，无需触发器：

1. 给name&id添加唯一约束

确保数据库可以识别重复的name&id组合：

ALTER TABLE your_table ADD CONSTRAINT unique_name_id UNIQUE (name, id);

2. 设置version字段的默认值

让新插入的记录自动使用初始version值：

ALTER TABLE your_table ALTER COLUMN version SET DEFAULT 1;

3. 使用UPSERT语句插入数据

每次插入时自动处理重复情况：

INSERT INTO your_table (name, id)
VALUES ('A', '1') -- 替换为你要插入的实际值
ON CONFLICT (name, id) DO UPDATE 
SET version = your_table.version + 1;

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