PostgreSQL实现字段原有值累加更新 替代MySQL ON DUPLICATE KEY UPDATE的方法

正确实现写法 #

INSERT INTO playerhours (uid, playtime, port) 
VALUES ('user1', 1337, 7780) 
ON CONFLICT(uid, port, date) 
  DO UPDATE SET playtime = playerhours.playtime + EXCLUDED.playtime;

说明 #

你之前的写法存在两个问题：

1. DO UPDATE子句中直接引用playtime会出现歧义，PostgreSQL无法判定你要取原有表中的存量值还是待插入的新值
2. 硬编码累加的数值灵活性很差，如果后续插入的数值有变动还要同步修改UPDATE逻辑

PostgreSQL的ON CONFLICT逻辑中内置了EXCLUDED虚拟表，专门用来存储本次INSERT操作中触发唯一约束冲突的待插入行数据，你只需要明确指定存量值来自目标表、增量值来自EXCLUDED表即可实现和MySQLON DUPLICATE KEY UPDATE完全一致的累加效果。

使用前请确认两个前置条件：

• 你已经在playerhours表上创建了(uid, port, date)三个字段的联合唯一约束
• 你本次INSERT的字段中没有显式指定date的话，确保date字段有非空默认值（比如默认当前日期），否则插入的行date为null无法触发唯一约束冲突

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