PostgreSQL中实现仅插入时自增SERIAL的UPDATE/INSERT操作需求

我完全懂你的困扰——用INSERT ... ON CONFLICT的时候，哪怕最终走的是更新逻辑，SERIAL类型的id序列也会被“浪费”一次，导致id出现没必要的间隙，而你的场景里更新远多于插入，这种情况就更闹心了。下面给你一个完美适配需求的方案：

核心思路：先尝试更新，失败再插入 #

我们可以利用PostgreSQL的事务和内置状态变量，先执行UPDATE操作，只有当没有匹配到需要更新的行时，再执行INSERT。这样只有真正插入新数据的时候，才会触发SERIAL序列的自增，完全避免了id的无意义增长。

具体实现（PL/pgSQL脚本） #

你可以把这个逻辑封装成一个事务块，保证原子性：

BEGIN;

-- 优先尝试更新目标行
UPDATE table_name
SET code_uses = code_uses + 1
WHERE user_id = 'some_random_id' AND item_name = 'some_random_name';

-- 如果UPDATE没有影响任何行（说明是新数据），执行插入
IF NOT FOUND THEN
    INSERT INTO table_name (user_id, item_name, code_uses)
    VALUES ('some_random_id', 'some_random_name', 1);
END IF;

COMMIT;

关键细节说明 #

• NOT FOUND是PostgreSQL的内置变量，当最近的DML语句（这里是UPDATE）没有影响任何行时，它的值为true，这时候才会触发后续的INSERT。
• 整个操作包裹在事务中，确保在并发场景下不会出现数据不一致的问题（比如两个请求同时尝试插入同一条数据时，只有一个能成功插入，另一个会执行更新）。
• 插入时我们显式指定了列名（跳过id），让PostgreSQL自动处理SERIAL的自增，这比写DEFAULT更清晰。

应用程序中的实现思路 #

如果是在应用代码里执行，逻辑也是一样的：

1. 开启事务
2. 执行UPDATE语句，获取受影响的行数
3. 如果受影响行数为0，执行INSERT语句
4. 提交事务

这样同样能达到只有INSERT时id才自增的效果，而且完全避开了INSERT ... ON CONFLICT带来的序列消耗问题。

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