事务中插入关联表时如何获取SERIAL类型自增主键值填充中间表

你使用的是PostgreSQL的SERIAL自增主键类型，有两种常用的可靠实现方式：

方法1：使用RETURNING子句捕获自增ID（推荐） #

RETURNING是PostgreSQL原生支持的语法，可以在插入操作执行后直接返回生成的主键值，单条、批量插入场景都适用。

单条食谱+单条食材关联示例（事务内链式插入）： #

BEGIN;

-- 插入食谱，返回生成的recipe_id
WITH inserted_recipe AS (
  INSERT INTO recipe (name, process) VALUES ('番茄炒蛋', '先炒鸡蛋再放番茄')
  RETURNING recipe_id
),
-- 插入食材，返回生成的ingredient_id
inserted_ingredient AS (
  INSERT INTO ingredient (name, type) VALUES ('番茄', '蔬菜')
  RETURNING ingredient_id
)
-- 直接用上面两个临时结果返回的ID插入关联表
INSERT INTO recipes_ingredients (recipe_id, ingredient_id)
SELECT ir.recipe_id, ii.ingredient_id FROM inserted_recipe ir, inserted_ingredient ii;

COMMIT;

如果是应用层代码操作，也可以执行插入语句后直接获取RETURNING返回的ID值，作为参数传入后续插入语句即可，逻辑和上述写法一致。

方法2：使用CURRVAL()函数获取当前会话最新序列值 #

SERIAL类型本质是绑定了序列的自增字段，同一个会话内对某张表执行插入后，可以用CURRVAL(序列名)拿到刚生成的主键值，不会被其他会话的插入操作干扰，事务内使用完全安全。
默认情况下SERIAL对应的序列名格式为表名_主键名_seq，你的场景下三张表的序列分别是recipe_recipe_id_seq、ingredient_ingredient_id_seq。
示例代码：

BEGIN;

-- 插入食谱
INSERT INTO recipe (name, process) VALUES ('番茄炒蛋', '先炒鸡蛋再放番茄');
-- 插入食材
INSERT INTO ingredient (name, type) VALUES ('番茄', '蔬菜');
-- 取当前会话刚生成的两个ID插入关联表
INSERT INTO recipes_ingredients (recipe_id, ingredient_id)
VALUES (
  CURRVAL('recipe_recipe_id_seq'),
  CURRVAL('ingredient_ingredient_id_seq')
);

COMMIT;

注意事项 #

• CURRVAL()仅在当前会话、且对应序列已经执行过NEXTVAL（即对应表已经执行过插入操作）时有效，不会跨会话读取其他插入操作生成的ID。
• 如果是单条食谱对应多条食材的批量插入场景，更推荐使用RETURNING的方式批量返回ID再批量插入关联表，逻辑更简洁。

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