PostgreSQL：INSERT语句中如何获取serial生成的新ID以拼接hierarchical_id？

可以实现，利用PostgreSQL的序列机制和CTE（公共表表达式）就能在单条语句内完成需求，同时获取新生成的ID。

核心思路 #

serial类型的id字段依赖对应的序列（默认命名为{schema}.{table}_id_seq），我们可以先通过nextval()原子性地获取序列的下一个值（即新记录的ID），再用这个ID拼接父级的hierarchical_id，最后完成插入并返回新ID。

具体SQL语句 #

方式一：硬编码序列名称（适用于已知序列名的场景）

WITH new_id AS (
    SELECT nextval('item_list.groups_id_seq') AS id
)
INSERT INTO item_list."groups" (id, "name", hierarchical_id, parent_id)
SELECT 
    id, 
    '新分组名称', 
    '0-12' || '-' || id,  -- 替换为实际的父级hierarchical_id
    12                     -- 替换为实际的parent_id
FROM new_id
RETURNING id;

方式二：动态获取序列名称（更健壮，适配序列名变动）

使用pg_get_serial_sequence()函数自动获取serial字段对应的序列，无需硬编码：

WITH new_id AS (
    SELECT nextval(pg_get_serial_sequence('item_list."groups"', 'id')) AS id
)
INSERT INTO item_list."groups" (id, "name", hierarchical_id, parent_id)
SELECT 
    id, 
    '新分组名称', 
    '0-12' || '-' || id,  -- 替换为实际的父级hierarchical_id
    12                     -- 替换为实际的parent_id
FROM new_id
RETURNING id;

说明 #

• nextval()是原子操作，并发场景下不会出现ID冲突；
• RETURNING id子句会返回新创建记录的ID，直接获取即可；
• 替换语句中的'新分组名称'、'0-12'、12为实际业务数据即可。

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