从 Posgtres 批量插入返回多个 SERIAL 值

Question

我正在使用 Postgres，使用 SERIAL 作为我的主键。插入一行后，我可以使用“RETURNING”或CURRVAL()获取生成的密钥。

现在我的问题是我想在事务中进行批量插入并获取所有生成的密钥。

我使用 RETURNING 和 CURRVAL 得到的只是最后生成的 id，其余结果将被丢弃。

我怎样才能让它返回所有这些？

谢谢

Answer 1

您可以将 RETURNING 与多个值一起使用：

psql=> create table t (id serial not null, x varchar not null);
psql=> insert into t (x) values ('a'),('b'),('c') returning id;
 id 
----
  1
  2
  3
(3 rows)

因此您想要更像这样的内容：

INSERT INTO AutoKeyEntity (Name,Description,EntityKey) VALUES
('AutoKey 254e3c64-485e-42a4-b1cf-d2e1e629df6a','Testing 5/4/2011 8:59:43 AM',DEFAULT)
returning EntityKey;
INSERT INTO AutoKeyEntityListed (EntityKey,Listed,ItemIndex) VALUES
(CURRVAL('autokeyentity_entityKey_seq'),'Test 1 AutoKey 254e3c64-485e-42a4-b1cf-d2e1e629df6a', 0),
(CURRVAL('autokeyentity_entityKey_seq'),'Test 2 AutoKey 254e3c64-485e-42a4-b1cf-d2e1e629df6a', 1),
(CURRVAL('autokeyentity_entityKey_seq'),'Test 3 AutoKey 254e3c64-485e-42a4-b1cf-d2e1e629df6a', 2)
returning EntityKey;
-- etc.

然后您必须从事务中的每个语句收集返回的 EntityKey 值。

您可以尝试在事务开始和结束时获取序列的当前值，并使用它们来确定使用了哪些序列值，但是 这不可靠：

此外，虽然多个会话保证分配
不同的序列值，这些值可能是由以下生成的
考虑所有会话时的顺序。例如，用
缓存设置为 10，会话 A 可能保留值 1..10 并返回
nextval=1，那么会话 B 可能会保留值 11..20 并返回
nextval=11 在会话 A 生成 nextval=2 之前。因此，随着
缓存设置，可以安全地假设nextval值是
按顺序生成； 缓存设置大于您的设置
应该只假设 nextval 值都是不同的，而不是
它们纯粹是按顺序生成的。此外，last_value 将
反映任何会话保留的最新值，无论是否
nextval 尚未返回它。

因此，即使您的序列的缓存值为 1，您的事务中仍然可以具有非连续的序列值。但是，如果序列的缓存值与事务中的插入数量匹配，您可能是安全的，但我猜这会太大而没有意义。

更新：我刚刚注意到（感谢提问者的评论）涉及到两个表格，在文字墙中有点迷失了。

在这种情况下，您应该能够使用当前的插入：

INSERT INTO AutoKeyEntity (Name,Description,EntityKey) VALUES
('AutoKey 254e3c64-485e-42a4-b1cf-d2e1e629df6a','Testing 5/4/2011 8:59:43 AM',DEFAULT)
returning EntityKey;
INSERT INTO AutoKeyEntityListed (EntityKey,Listed,ItemIndex) VALUES
(CURRVAL('autokeyentity_entityKey_seq'),'Test 1 AutoKey 254e3c64-485e-42a4-b1cf-d2e1e629df6a', 0),
(CURRVAL('autokeyentity_entityKey_seq'),'Test 2 AutoKey 254e3c64-485e-42a4-b1cf-d2e1e629df6a', 1),
(CURRVAL('autokeyentity_entityKey_seq'),'Test 3 AutoKey 254e3c64-485e-42a4-b1cf-d2e1e629df6a', 2);
-- etc.

并从 AutoEntityKey 上的插入中一次获取一个 EntityKey 值。可能需要某种脚本来处理返回值。您还可以将 AutoKeyEntity 和相关的 AutoKeyEntityListed INSERT 包装在函数中，然后使用 INTO 获取 EntityKey 值并从函数返回它：

INSERT INTO AutoKeyEntity /*...*/ RETURNING EntityKey INTO ek;
/* AutoKeyEntityListed INSERTs ... */
RETURN ek;

Answer 2

您可以使用此方法预先分配连续的 id：

SELECT setval(seq, nextval(seq) + num_rows - 1, true) as stop

它应该是调用 nextval() 无数次的更快替代方案。

您还可以将 ids 存储在临时表中：

create temporary blah (
  id int
) on commit drop;

insert into table1 (...) values (...)
returning id into blah;

在 postgres 9.1 中，可以使用 CTE：

with
ids as (
insert into table1 (...) values (...)
    returning id
)
insert into table2 (...)
select ...
from ids;

Answer 3

在您的应用程序中，从序列中收集值：

SELECT nextval( ... ) FROM generate_series( 1, number_of_values ) n

使用这些值创建行，然后简单地插入（使用多行插入）。它是安全的（SERIAL 按照您的预期工作，不重用值、并发证明等）并且快速（您一次插入所有行，无需多次客户端-服务器往返）。

Answer 4

更详细地回复 Scott Marlowe 的评论：

假设您有一个树表，其中通常的parent_id 引用自身，并且您想要导入一个大的记录树。问题是您需要知道父级的 PK 值才能插入子级，因此可能需要大量单独的 INSERT 语句。

因此，解决方案可能是：

• 在应用程序中构建树，
• 获取与要插入的节点一样多的序列值，使用“SELECT nextval(...) FROMgenerate_series(1, number_of_values) n”（值的顺序无关紧要）
• 将这些主键值分配给节点
• 进行批量插入（或复制）遍历树结构，因为用于关系的 PK 是已知的

Answer 5

有三种方法可以做到这一点。使用 currval()、使用返回或编写一个存储过程将这些方法中的任何一个包装在一个漂亮的小毯子中，这样您就不必在半客户端半 postgres 中完成这一切。

Currval method:
begin;
insert into table a (col1, col2) values ('val1','val2');
select currval('a_id_seq');
123  -- returned value
-- client code creates next statement with value from select currval
insert into table b (a_fk, col3, col4) values (123, 'val3','val4');
-- repeat the above as many times as needed then...
commit;

Returning method:
begin;
insert into table a (col1, col2) values ('val1','val2'), ('val1','val2'), ('val1','val2') returning a_id; -- note we inserted three rows
123  -- return values
124
126
insert into table b (a_fk, col3, col4) values (123, 'val3','val4'), (124, 'val3','val4'), (126, 'val3','val4');
commit;

Answer 6

执行FOR循环并逐条处理记录。它的性能可能较差，但它是并发安全的。

示例代码：

DO $
  DECLARE r record;
    
  BEGIN
    FOR r IN SELECT id FROM {table} WHERE {condition} LOOP
      WITH idlist AS (
        INSERT INTO {anotherTable} ({columns}) VALUES ({values})
        RETURNING id
      UPDATE {table} c SET {column} = (SELECT id FROM idlist) WHERE c.id = {table}.id;
    END LOOP;
END $;