PostgreSQL中两表数据同步需求：按NAME字段合并或新增记录

前提准备 #

首先需要为 result 表的 name 字段添加唯一约束，确保能准确判断同名记录是否存在：

ALTER TABLE result ADD CONSTRAINT unique_result_name UNIQUE (name);

方案一：触发器自动维护（推荐） #

通过创建触发器，实现向 purchases 插入数据时自动同步更新 result 表，无需手动执行额外语句。

1. 创建触发器函数 #

CREATE OR REPLACE FUNCTION update_result_table()
RETURNS TRIGGER AS $$
BEGIN
    -- 尝试更新result中对应name的记录，累加qty
    UPDATE result
    SET qty = result.qty + NEW.qty,
        date = NEW.date -- 可选：将date更新为最新插入的时间，可根据需求删除
    WHERE name = NEW.name;

    -- 若未找到匹配记录，则插入新行
    IF NOT FOUND THEN
        INSERT INTO result (id, name, qty, date, profit, sold)
        VALUES (NEW.id, NEW.name, NEW.qty, NEW.date, 0, 0); -- profit、sold初始值设为0，可按需调整
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. 创建触发器 #

CREATE TRIGGER trigger_after_insert_purchases
AFTER INSERT ON purchases
FOR EACH ROW
EXECUTE FUNCTION update_result_table();

之后每次向 purchases 插入数据时，会自动完成：

• 若 name 在 result 中已存在，累加对应行的 qty
• 若 name 不存在，在 result 中新增一行，qty 与插入行一致

方案二：手动执行同步语句 #

如果不需要自动触发，可在每次插入 purchases 后手动执行以下语句（以插入id为1的记录为例）：

1. 先插入 purchases：

INSERT INTO purchases (id, name, qty, date, price, about)
VALUES (1, '商品A', 5, '2024-05-20 10:00:00', 100.00, '测试商品');

2. 同步更新 result 表：

INSERT INTO result (id, name, qty, date, profit, sold)
SELECT id, name, qty, date, 0, 0
FROM purchases
WHERE id = 1 -- 对应刚插入的purchases记录id
ON CONFLICT (name) DO UPDATE
SET qty = result.qty + EXCLUDED.qty,
    date = EXCLUDED.date;

注意事项 #

• 必须保证 result 表的 name 字段唯一，否则会出现多条同名记录导致逻辑错误
• profit 和 sold 字段的初始值可根据业务需求调整为 NULL 或其他数值
• 若 purchases 的 id 存在重复场景，需调整同步逻辑中的匹配条件

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