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

Returning multiple SERIAL values from Posgtres batch insert

提问by Nicolas

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

我使用 Postgres，SERIAL用作我的主键。插入一行后，我可以使用 ' RETURNING' 或CURRVAL().

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

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

All I get with RETURNINGand CURRVALis the last generated id, the rest of the result get discarded.

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

How can I get it to return all of them?

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

Thanks

谢谢

回答by mu is too short

You can use RETURNINGwith multiple values:

您可以使用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)

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.

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

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

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:

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

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 cachesetting of 10, session A might reserve values 1..10 and return nextval=1, then session B might reserve values 11..20 and return nextval=11before session A has generated nextval=2. Thus, with a cachesetting of one it is safe to assume that nextvalvalues are generated sequentially; with a cachesetting greater than one you should only assume that the nextvalvalues are all distinct, not that they are generated purely sequentially. Also, last_valuewill reflect the latest value reserved by any session, whether or not it has yet been returned by nextval.

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

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

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

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:

在这种情况下，您应该能够使用当前的 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.

And grab the EntityKeyvalues 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 AutoKeyEntityand related AutoKeyEntityListedINSERTs in a function, then use INTOto grab the EntityKeyvalue and return it from the function:

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

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

回答by Denis de Bernardy

you can pre-assign consecutive ids using this:

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

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

it should be a faster alternative to calling nextval()gazillions of times.

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

you could also store ids in a temporary table:

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

create temporary blah (
  id int
) on commit drop;

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

in postgres 9.1, can able to use CTEs:

在 postgres 9.1 中，可以使用 CTE：

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

回答by peufeu

In your application, gather values from the sequence :

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

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

Create your rows using those values, and simply insert (using a multiline insert). It's safe (SERIAL works as you'd expect, no reuse of values, concurrent proof, etc) and fast (you insert all the rows at once without many client-server roundtrips).

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

回答by peufeu

Replying to Scott Marlowe's comment in more detail :

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

Say you have a tree table with the usual parent_id reference to itself, and you want to import a large tree of records. Problem is you need the parent's PK value to be known to insert the children, so potentially this can need lots of individual INSERT statements.

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

So a solution could be :

所以一个解决方案可能是：

• build the tree in the application
• grab as many sequence values as nodes to insert, using "SELECT nextval( ... ) FROM generate_series( 1, number_of_values ) n" (the order of the values does not matter)
• assign those primary key values to the nodes
• do a bulk insert (or COPY) traversing the tree structure, since the PKs used for relations are known

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

回答by Scott Marlowe

There are three ways to do this. Use currval(), use returning, or write a stored procdure to wrap either of those methods in a nice little blanket that keeps you from doing it all in half client half postgres.

有三种方法可以做到这一点。使用 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;