Postgresql Multi Insert...Returning with Multiple Columns

INSERT INTO ... RETURNING multiple columns (PostgreSQL)

@corvinusz answer was wrong for 8.3 but gave me a great idea that worked so thanks!

INSERT INTO my_table VALUES (a, b, c)
RETURNING (SELECT x FROM x_table WHERE xid = a),
  (SELECT y FROM y_table WHERE yid = b),
  (SELECT z FROM z_table WHERE zid = c)

I have no idea why the way it's stated in the question is invalid but at least this works.

PostgreSQL multi INSERT...RETURNING with multiple columns

Use data-modifying CTEs to chain your three INSERTs. Something like this:

WITH ins1 AS (
   INSERT INTO table1 (username, name,  surname)
   VALUES ('johnee','john','smith')
   RETURNING user_id
   )
, ins2 AS (
   INSERT INTO table2 (user_id, password)
   SELECT ins1.user_id, 'secret'
   FROM   ins1                            -- nothing to return here
   )
INSERT INTO table3 (user_id, adress, city, phone)
SELECT ins1.user_id, ...
FROM   ins1
RETURNING user_id;

• It's typically best to add a column definition list for INSERTs (except for special cases). Else, if the table structure changes, your code might break in surprising ways.

• I omitted columns where you would just enter DEFAULT. Defaults are inserted automatically. Shorter, same result.

• The final, optional RETURNING returns the resulting user_id - obviously from a sequence or some other default. It's actually the user_id from table3, but that's the same unless you have some triggers or other magic interfering.

More about data-modifying (a.k.a. "writable") CTEs:

• Are SELECT type queries the only type that can be nested?

Use returned value of INSERT ... RETURNING in multiple following inserts

You can create one CTE with the rows you want to insert and then use that as the source for the actual insert:

WITH temp_table AS (
  INSERT INTO hosts (name) VALUES ('Host C') 
  RETURNING host_id AS last_hostid
), new_data (name, iface_ip) AS (
  values 
    ('eth0', '192.168.1.1'::inet),
    ('eth1', '192.168.2.1'::inet),
    ('eth2', '192.168.3.1'::inet)
)
INSERT INTO interfaces (host_id, name, iface_ip) 
SELECT last_hostid, nd.name, nd.iface_ip
FROM new_data as nd, temp_table;

The (implicit) cross join in the SELECT doesn't matter as temp_table only return a single row.

Update or Insert (multiple rows and columns) from subquery in PostgreSQL

For the UPDATE

Use:

UPDATE table1 
   SET col1 = othertable.col2,
       col2 = othertable.col3 
  FROM othertable 
 WHERE othertable.col1 = 123;

For the INSERT

Use:

INSERT INTO table1 (col1, col2) 
SELECT col1, col2 
  FROM othertable

You don't need the VALUES syntax if you are using a SELECT to populate the INSERT values.

Insert into Multiple Columns from a Grouped by Subquery

try like below if want to use subsqery

INSERT INTO TABLE (COLUMN_A, COLUMN_B, COLUMN_C)              -
SELECT 'Label A', CATEGORY,  cnt from                                          
(SELECT CATEGORY, COUNT(1) as cnt FROM SUB_TABLE GROUP BY CATEGORY) a

in fact don't need sub-query

 INSERT INTO TABLE (COLUMN_A, COLUMN_B, COLUMN_C)              -
SELECT 'Label A', CATEGORY,  COUNT(1) as cnt from                                          
  FROM SUB_TABLE GROUP BY CATEGORY

demo link

Postgresql multi insert from array of composite type and one additional column

I think you mean "insert multiple rows" rather than columns.

Assuming that is correct, I think you are looking for a function like this:

create or replace function items_insert(_id bigint, _items item[]) 
  returns void 
as
$$
  insert into items (id, a, b)
  select _id, it.*
  from unnest(_items) as it(a,b);
$$
language sql;

Online example

Returning multiple SERIAL values from Posgtres batch insert

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.

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:

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.

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.

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;

One INSERT with multiple SELECT

Assumptions

• You want to link the newly inserted row in main_phrase to the row(s) in main_groupecategories with the same description.
• main_phrase.id is a serial column.

Explanation for Error

You cannot refer to any tables (including CTE) in a free-standing VALUES expression, you would have to use SELECT with a FROM clause. But there is a better solution. See below.

Better Query

Use a data-modifying CTE instead to make the whole operation shorter, safer and faster:

WITH p AS (
   INSERT INTO main_phrase (description)
   VALUES ('Mot commun féminin pluriel animaux')  -- provide description once
   RETURNING id, description  -- and reuse it further down
   )
INSERT INTO main_phrasegroupecategories (phrase_id, groupe_categories_id)
SELECT p.id, g.id
FROM   p
JOIN   main_groupecategories g USING (description);

If you want to use any values of the new rows, have them returned immediately with another RETURNING clause to the second INSERT.

Why would you have the same description redundantly in both tables of your (presumed) many-to-many relationship? Might be a problem in your database design.

Related:

• PostgreSQL multi INSERT...RETURNING with multiple columns
• SELECT * FROM NEW TABLE equivalent in Postgres
• Combining INSERT statements in a data-modifying CTE with a CASE expression

---

Related Topics