如何基于某列最大值向PostgreSQL数据库插入记录？

我有一张包含name字段和number字段的表（两者均不唯一），初始数据如下：

| name    | number |
|---------+--------|
| Bob     |      2 |
| Bob     |      8 |
| Charlie |      4 |

需要实现以下逻辑的插入操作：

• 给定一个名称，若该名称不存在于表中，则插入一条name为该名称、number为1的新记录；
• 若该名称已存在，则插入一条number为该名称对应最大number值加1的新记录。

示例效果 #

• 插入"Bob"后，表新增一条Bob、9的记录；
• 插入"Steve"后，表新增一条Steve、1的记录。

此需求用于Web服务，必须考虑竞态条件风险。我曾尝试用CASE语句实现，但无法正确提取MAX值，代码如下：

SELECT name,
        CASE
                WHEN (SELECT COUNT(*) FROM t WHERE name = 'Bob') != 0 THEN (SELECT MAX(number) FROM t WHERE name = 'Bob') + 1 ELSE 1
        END
FROM t;

1. 基础实现（无竞态场景） #

直接用INSERT ... SELECT语句一次性完成判断与插入，逻辑简洁且能正确获取MAX值：

-- MySQL/MariaDB写法
INSERT INTO t (name, number)
SELECT 'Bob', COALESCE((SELECT MAX(number) FROM t WHERE name = 'Bob'), 0) + 1
FROM dual;

-- SQLite/PostgreSQL可省略FROM dual
INSERT INTO t (name, number)
SELECT 'Bob', COALESCE((SELECT MAX(number) FROM t WHERE name = 'Bob'), 0) + 1;

COALESCE函数会返回第一个非NULL值：若目标名称不存在，MAX(number)返回NULL，就用0+1得到1；若存在，则用最大number值加1。

2. 竞态条件处理（Web服务必用） #

高并发场景下，多个请求同时插入同一名称时，可能出现多请求读取相同MAX值、插入重复number的问题，需用数据库原子操作或锁机制解决：

方案A：事务加行锁（通用） #

通过SELECT ... FOR UPDATE在事务中锁定目标名称的相关行，阻塞其他事务的并发修改：

START TRANSACTION;
-- 锁定该name对应的所有行，防止其他事务修改
SELECT MAX(number) FROM t WHERE name = 'Bob' FOR UPDATE;
-- 执行插入
INSERT INTO t (name, number)
SELECT 'Bob', COALESCE((SELECT MAX(number) FROM t WHERE name = 'Bob'), 0) + 1;
COMMIT;

方案B：高隔离级别事务（通用） #

将事务隔离级别设为SERIALIZABLE（最高级别），避免幻读与竞态：

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
INSERT INTO t (name, number)
SELECT 'Bob', COALESCE((SELECT MAX(number) FROM t WHERE name = 'Bob'), 0) + 1;
COMMIT;

注意：此方式会降低并发性能，适合对一致性要求极高的场景。

方案C：PostgreSQL专属写法 #

直接在插入的SELECT语句中加FOR UPDATE，实现原子性：

INSERT INTO t (name, number)
SELECT 'Bob', COALESCE(MAX(number), 0) + 1
FROM t WHERE name = 'Bob' FOR UPDATE;

原CASE语句的问题 #

你的CASE语句是查询表中所有行并为每行计算一次值，这和插入单条记录的需求不匹配。正确的逻辑应该是生成要插入的单条数据，而非遍历原有表的所有行。

内容的提问来源于stack exchange，提问作者My Hero Stackademia