Return multiple SERIAL values ​​from a Posgtres batch insert

Question

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

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

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

How can I get him to return all of them?

thanks

Answer 1

You can use RETURNING with several 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 need something more:

 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 will need to collect the EntityKey return values ​​from each transaction statement.

You can try to capture the current value of the sequence at the beginning and end of the transaction and use them to determine which values ​​were used, but which are not reliable

In addition, although multiple sessions guarantee that different sequence values ​​are highlighted, values ​​can be obtained from the sequence when all sessions are considered. For example, from cache 10, session A can reserve the values ​​1.10 and return nextval=1 , then session B can reserve the values ​​11..20 and return nextval=11 before session A generated nextval = 2. Thus, from the cache, you can confidently assume that nextval values ​​are generated sequentially; with a cache setting more than you should only assume that nextval values ​​are different and not that they are generated purely sequentially. In addition, last_value will reflect the last value reserved for any session, regardless of whether it has not yet been returned by nextval .

So, even if your sequences have cache values ​​of one, you can still have non-contiguous sequence values ​​in your transaction. However, you can be safe if the sequence cache value matches the number of INSERTs in the transaction, but I assume it will be too large to make sense.

UPDATE . I noticed (thanks to the commenters on the comments) that there are two tables, a little lost in the wall of the text.

In this 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. 

And take the EntityKey values ​​one at a time from INSERT to AutoEntityKey . It might take some script to process the RETURNING values. You can also wrap AutoKeyEntity and its associated AutoKeyEntityListed INSERT in a function, and then use INTO to get the EntityKey value and return it from the function

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

Answer 2

you can preassign consecutive identifiers using this:

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

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

you can also store identifiers in a temporary table:

 create temporary blah ( id int ) on commit drop; insert into table1 (...) values (...) returning id into blah; 

in postgres 9.1, can use CTE:

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

Answer 3

In your application, collect values ​​from the sequence:

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

Create your lines using these values ​​and just paste (using multi-line insert). It's safe (SERIAL works as you expected, without reusing values, parallel proof, etc.) and fast (you insert all rows at once without many calls to the client server).

Answer 4

Response to Scott Marlow's comment in more detail:

Say you have a tree table with a regular parent_id link to yourself, and you want to import a large record tree. The problem is that you need to know the parent value of PK to insert children, so potentially this may require many separate INSERT statements.

So the solution could be:

• build a tree in the application
• capture as many sequence values ​​as insert nodes using "SELECT nextval (...) FROM generate_series (1, number_of_values) n" (the order of the values ​​doesn't matter)
• assign these primary key values ​​to nodes
• do a volume insert (or COPY) passing through the tree structure, since PKs used for relationships are known

Answer 5

There are three ways to do this. Use currval (), use return, or write down the stored procdure to wrap any of these methods in a beautiful little blanket that prevents you from doing this in half half pixels.

 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;