Multiple inserts using one cte postgres

Question

I am beginner in postgres. while working on a dummy project I came across this problem.

I have two tables let say t1 and t2.The t1 having 1->Many relation with t2.

I am trying to write an SQL statement which first inserts data in t1 and using id from t1 inserts the multiple rows in t2.

Something like this.

WITH ins AS (
    INSERT INTO t1(t1_col) 
    VALUES (4)
    RETURNING t1_id
)
INSERT INTO t2(t1_id, t2_col) VALUES (ins.t1_id, 3), (ins.t1_id, 4)...

t1 structure -> (t1_id primary_key serial, t1_col integer).

t2 structure -> (t2_id primary_key serial, t1_id integer, t2_col integer).

What is the correct way to do this.

Thank you in advance.

My mistake, Yes I meant serial.

TBKJ
– TBKJ

2020-08-05 22:28:59 +00:00
Commented Aug 5, 2020 at 22:28 — TBKJ
– TBKJ, Commented Aug 5, 2020 at 22:28

Игорь Тыра · Accepted Answer · 2020-08-05 17:28:10Z

5

except for inserting with VALUES clause you can insert the result of SELECT. in general form it will be:

WITH ins AS (
  INSERT INTO table1(target columns)
  VALUES (some values) -- or -- SELECT something FROM somewhere
  RETURNING some_id
)
INSERT INTO table2(target columns)
SELECT ins.some_id, other columns or expressions
FROM ins;

variant for multiple rows (fixed list)

WITH ins AS (
  INSERT INTO table1(target columns)
  VALUES (some values) -- or -- SELECT something FROM somewhere
  RETURNING some_id
)
INSERT INTO table2(target columns)
SELECT ins.some_id, UNNEST(ARRAY[3,4,...])
FROM ins;

where 3,4.... is the list of values

edited Aug 5, 2020 at 17:28

answered Aug 5, 2020 at 16:48

Игорь Тыра

9755 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

TBKJ Over a year ago

I want to add multiple rows in table2, using above block I can add only one row to table2.

Игорь Тыра Over a year ago

for multiple rows you may use JOIN to some table3 or just UNNEST(ARRAY[some values]) as one column of select

TBKJ Over a year ago

I am using this syntax for multiple columns insertion using unnest INSERT INTO table2(t1_id, col1, col2) SELECT ins.some_id, UNNEST(ARRAY[3,4,...]), UNNEST(ARRAY[1, 2 ...]) FROM ins; is this the correct syntax.

Игорь Тыра Over a year ago

the syntax is correct, as an example, but real arrays are without ellipsis, bus just comma separated items. eg array[1,2,3] or array[5,6,7,8,9]

Mike Organek · Accepted Answer · 2020-08-05 17:39:38Z

0

This will do it in a single statement.

WITH ins AS (
    INSERT INTO t1(t1_col) 
    VALUES (4)
    RETURNING t1_id
)
INSERT INTO t2(t1_id, t2_col) 
SELECT ins.t1_id, v.t2_col
  from ins
 cross join (VALUES (3), (4)) as v(t2_col)
;

If you are running this from a host language and can pass the t2 values as an array, please comment because this can be simplified.

I would build it something like this for use with a host language:

with invars as (
  select $1 as t1_col, $2::text[] as t2_cols
), ins as (
  insert into t1 (t1_col)
  select t1_col 
    from invars
  returning t1_id 
)
insert into t2 (t1_id, t2_col)
select ins.t1_id, unnest(invars.t2_cols) as t2_col
  from ins
 cross join invars;

Then, from the host, I would pass t1_col and an array of t2_col values as parameters to the query.

edited Aug 5, 2020 at 17:39

answered Aug 5, 2020 at 16:54

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

1 Comment

TBKJ Over a year ago

Yes, I am building my statement in nodejs

Stefanov.sm · Accepted Answer · 2020-08-05 20:03:06Z

0

An anonymous plpgsql block will do.

do language plpgsql
$$
declare
  t1id t1.t1_id%type;
begin 
  INSERT INTO t1(t1_col) VALUES (4) RETURNING t1_id INTO t1id;
  INSERT INTO t2(t1_id, t2_col) 
  VALUES (t1id, 3), (t1id, 4)...;
end;
$$;

edited Aug 5, 2020 at 20:03

answered Aug 5, 2020 at 16:38

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

3 Comments

TBKJ Over a year ago

Is there any performance issue with using this block vs single statement using WITH.

Игорь Тыра Over a year ago

performance is not a question while working with micro-data like this.

Stefanov.sm Over a year ago

Generally speaking plpgsql will be slower due to context switching. But if it's clearer for me and my team then I would sacrifice some ticks for readability and maintainability. I changed integer with t1.t1_id%type to make it more generic.

user330315 · Accepted Answer · 2020-08-05 20:11:38Z

0

No need for a CTE or variables, you can use lastval() to get the last generated identity (or sequence) value:

INSERT INTO t1(t1_col) 
VALUES (4);

INSERT INTO t2(t1_id, t2_col) 
VALUES 
(lastval(), 3), 
(lastval(), 4),
...

answered Aug 5, 2020 at 20:11

user330315

1 Comment

TBKJ Over a year ago

I tried this approach, but this is not working whene there is multiple rows. on first insert it returns the id of inserted row in t1 but in second insert, lastval() returns the last id inserted in t2.

Collectives™ on Stack Overflow

Multiple inserts using one cte postgres

4 Answers 4

4 Comments

1 Comment

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

1 Comment

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related