Insert into 2 related tables from a single SELECT using POSTGRES

Question

Trying to move data from a single table cc into 2 tables aa and bb related by a foreign key:

Table cc
====
data_a, data_b


Table aa
====
id, data_a


Table bb
====
id, data_b, aa_id

Table bb has a foreign key aa_id. I need to create a row in aa with cc.data_a and a row in bb with cc.data_b and the newly created aa.id.

Is this possible, and if so, how?

Gordon Linoff · Accepted Answer · 2018-09-25 00:48:53Z

1

Is this what you want?

with ar as (
      insert into aa (data_a)
         select distinct data_a
         from cc
         returning *
     )
insert into bb (data_b, aa_id)
    select cc.data_b, ar.id
    from cc join
         ar
         on cc.data_a = ar.data_a;

This assumes that you have defined a and b in advance with serial id columns.

edited Sep 25, 2018 at 0:48

answered Sep 24, 2018 at 20:05

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

5 Comments

New Dev Over a year ago

Does it not require data_a to be unique? I probably should have specified that the data doesn't satisfy this condition. It's important to maintain the pairings of data_a and data_b but the data could be anything.

Gordon Linoff Over a year ago

@NewDev . . . select distinct ensures that a has unique values in data_a.

New Dev Over a year ago

I see. I guess I oversimplified the example. I need each row in cc to create a new row in both aa and bb, regardless of whether there may be duplicates in data. In real-world problem there are many columns of data.

Gordon Linoff Over a year ago

@NewDev . . . Why would you want duplicates in aa? That just doesn't make sense. The purpose of splitting into multiple tables is to reduce the number of duplicates.

New Dev Over a year ago

table cc is seed data into aa and bb that have other columns, in which the data will need to evolve independently.

Collectives™ on Stack Overflow

Insert into 2 related tables from a single SELECT using POSTGRES

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related