PostgreSQL批量插入后如何获取最后插入行的ID？

问题根源 #

你批量插入时returning id into _query报错，是因为这条语句会返回多个ID值，而_query是单个int变量，无法存储多行结果，导致类型不匹配。

可行解决方案 #

方案1：用数组接收所有插入的ID，再取最后一个 #

声明数组类型变量存储所有返回的ID，之后通过数组长度定位最后一个元素：

do $$
declare _ids int[];
begin
    drop table if exists tTest;
    create temp table tTest (id serial primary key, name text);

    -- 批量插入，将所有返回的ID存入数组
    insert into tTest(name)
    values ('name'), ('name1'), ('name2') returning id into _ids;

    -- 取数组最后一个元素，即最后插入的ID
    RAISE NOTICE '%', _ids[array_length(_ids, 1)];
end;
$$;

方案2：使用currval()函数（推荐） #

对于serial类型的主键，PostgreSQL会自动创建对应的序列（命名规则为表名_字段名_seq，比如tTest_id_seq）。currval()函数会返回当前会话中该序列最后一次生成的值，也就是批量插入时最后一行的ID，完全符合你需要的类似scope_identity()的功能：

do $$
declare _last_id int;
begin
    drop table if exists tTest;
    create temp table tTest (id serial primary key, name text);

    -- 批量插入
    insert into tTest(name)
    values ('name'), ('name1'), ('name2');

    -- 获取当前会话中序列的最后值
    _last_id := currval('tTest_id_seq');
    RAISE NOTICE '%', _last_id;
end;
$$;

对应你业务中insert into Table1 select ... from Table2的场景，只需替换表名和序列名即可：

insert into Table1(a, b, c) select a, b, c from Table2;
_last_id := currval('table1_id_seq');

方案3：用临时表存储返回结果，再取最大值 #

如果需要保留所有插入的ID同时获取最后一个，可以用临时表中转：

do $$
declare _last_id int;
begin
    drop table if exists tTest;
    create temp table tTest (id serial primary key, name text);
    -- 创建临时表存储插入的ID
    create temp table temp_ids(id int);

    -- 批量插入，将ID写入临时表
    insert into tTest(name)
    values ('name'), ('name1'), ('name2') returning id into temp_ids;

    -- 从临时表取最大ID
    select max(id) into _last_id from temp_ids;
    RAISE NOTICE '%', _last_id;
end;
$$;

注意事项 #

• currval()只能在当前会话中使用，且必须先触发过序列的nextval()（插入操作会自动触发），否则会报错。
• 禁止直接用select max(id) from Table1，高并发场景下会包含其他会话插入的数据，导致结果错误。

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