postgresql: copy table1 to table2 and table3, but use returned table2 values to insert into table3

Question

I have three tables:

table1(id, name, foo)
table2(id, name)
table3(id, id2, name, foo, bar)

And I'd like to copy data from table1 to tables 2 & 3, but table3.id2 has to correspond to the row in table2. I think I need to do something with the RETURNING command, but I have been unsuccessful.

WITH oldstuff AS (
    SELECT name, foo, 'some value' AS bar
    FROM table1
  ),
  newstuff AS (
    INSERT INTO table2 (name)
    SELECT name FROM oldstuff
    RETURNING id AS id2 /* but i also need oldstuff.name, oldstuff.foo,and oldstuff.bar */
  )
INSERT INTO table3 (id2, name, foo, bar)
SELECT * FROM newstuff; /* I cant just do a join here because the fields arent unique */

score 1 · Accepted Answer · edited Jan 23 '18 at 15:42

Conceptually, you would do:

WITH oldstuff AS (
      SELECT name, foo, 'some value' AS bar
      FROM table1
     ),
     newstuff AS (
      INSERT INTO table2 (name)
          SELECT name
          FROM oldstuff
          RETURNING *
    )
INSERT INTO table3 (id2, name, foo, bar)
    SELECT ns.id, ns.name, os.foo, os.bar
    FROM newstuff ns join
         oldstuff os
         on ns.name = os.name;

From what you say, this doesn't do exactly what you want because there is not a unique name in newstuff. Either generate a unique id for the original table or only insert unique data into it:

      INSERT INTO table2 (name)
          SELECT DISTINCT name
          FROM oldstuff
          RETURNING *;

Hmmm . . . There might be a kludgy way:

WITH oldstuff AS (
      SELECT name, foo, 'some value' AS bar
      FROM table1
     ),
     newstuff AS (
      INSERT INTO table2 (name)
          SELECT name
          FROM oldstuff
          RETURNING *
    )
INSERT INTO table3 (id2, name, foo, bar)
    SELECT ns.id, ns.name, os.foo, os.bar
    FROM (SELECT ns.*, ROW_NUMBER() OVER (PARTITION BY name ORDER BY name) as seqnum
          FROM newstuff ns
         ) ns JOIN
         (SELECT os.*, ROW_NUMBER() OVER (PARTITION BY name ORDER BY name) as seqnum
          FROM oldstuff os
         ) os
         on ns.name = os.name and ns.seqnum = os.seqnum;

This will work with duplicate names, and you get exactly one match in the final table.

Thanks! I did it in a slightly different order after coming across [this post](https://stackoverflow.com/questions/29256888/insert-into-from-select-returning-id-mappings/29263402#29263402), and ordered by id instead of name to make sure the correct row was lined up in the row_number — dem7w2, Jan 23 '18 at 20:26

postgresql: copy table1 to table2 and table3, but use returned table2 values to insert into table3

1 Answers1