从Posgtres批处理插入中返回多个SERIAL值 [英] Returning multiple SERIAL values from Posgtres batch insert

问题描述

我正在使用Postgres，使用 SERIAL 作为我的主键。在插入一行之后，我可以使用' RETURNING '或 CURRVAL（）来获取生成的密钥。

Im working with Postgres, using SERIAL as my primary key. After I insert a row I can get the generated key either by using 'RETURNING' or CURRVAL().

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

Now my problem is that I want to do a batch insert inside a transaction and get ALL the generated keys.

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

All I get with RETURNING and CURRVAL is the last generated id, the rest of the result get discarded.

如何获取返回的所有内容？

How can I get it to return all of them?

谢谢

推荐答案

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

You can use RETURNING with multiple values:

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)

所以您想要更多类似的东西：

So you want something more like this:

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 来自事务中每个语句的值。

And then you'll have to gather the returned EntityKey values from each statement in your transaction.

您可以尝试在序列的开始和结束处获取序列的当前值。交易并使用这些交易来确定使用了哪些序列值，但是不可靠：

You could try to grab the sequence's current value at the beginning and end of the transaction and use those to figure out which sequence values were used but that is not reliable:

此外，尽管保证多个会话分配
个不同的序列值，但这些值可能是从$ b生成的当考虑所有会话时，$ b顺序。例如，将
cache 设置为10，会话A可能会保留值1..10并返回
nextval = 1 ，那么会话B可能会保留值11..20，并在会话A生成nextval = 2之前返回
nextval = 11 。因此，在
cache 设置为1的情况下，可以安全地假设 nextval 值是顺序生成的
。如果 cache 设置大于1，则
仅应假设 nextval 的值都是不同的，而不是
完全按顺序生成。此外， last_value 将使
反映任何会话保留的最新值，无论
是否已由 nextval返回。 。

Furthermore, although multiple sessions are guaranteed to allocate distinct sequence values, the values might be generated out of sequence when all the sessions are considered. For example, with a cache setting of 10, session A might reserve values 1..10 and return nextval=1, then session B might reserve values 11..20 and return nextval=11 before session A has generated nextval=2. Thus, with a cache setting of one it is safe to assume that nextval values are generated sequentially; with a cache setting greater than one you should only assume that the nextval values are all distinct, not that they are generated purely sequentially. Also, last_value will reflect the latest value reserved by any session, whether or not it has yet been returned by nextval.

因此，即使您的序列具有 cache 值，交易中仍然可以有不连续的序列值。但是，如果序列的 cache 值与您的事务中INSERT的数量匹配，则可能是安全的，但我想那会太大而无法理解。

So, even if your sequences have cache values of one you can still have non-contiguous sequence values in your transaction. However, you might be safe if the sequence's cache value matches the number of INSERTs in your transaction but I'd guess that that's going to be too large to make sense.

更新：我刚刚注意到（由于发问者的评论），其中涉及两个表，在文本墙中有些丢失。

UPDATE: I just noticed (thanks to the questioner's comments) that there are two tables involved, got a bit lost in the wall of text.

在这种情况下，您应该可以使用当前的插入内容：

In that case, you should be able to use the current INSERTS:

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.

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

And grab the EntityKey values one at a time from the INSERTs on AutoEntityKey. Some sort of script might be needed to handle the RETURNING values. You could also wrap the AutoKeyEntity and related AutoKeyEntityListed INSERTs in a function, then use INTO to grab the EntityKey value and return it from the function:

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

这篇关于从Posgtres批处理插入中返回多个SERIAL值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！