CODEWITHSUNDEEP
postgresql
Burleigh Bear
Burleigh Bear

Reputation: 3314

Multiple inserts across three tables capturing a serial ID column in postgres

I have three tables, which I've abbreviated into the following

create table top (top_id serial, top_name text);
create table mid (mid_id serial, top_id integer, mid_name text);
create table bot (bot_id serial, mid_id integer, bot_name text);

what I want to achieve is

So, the final result might look like

top
----
1, first_top
2, second_top

mid
---
1, 1, first_mid_for_first_top
2, 1, second_mid_for_first_top
3, 2, first_mid_for_second_top

bot
---
1, 1, first_bot_for_first_mid
2, 1, second_bot_for_first_mid
3, 3, first_bot_for_third_mid

My first approach was to try to have a bunch of nested CTEs, but I couldn't make that into valid syntax (and on consideration, I think that it's not the right approach).

My second approach was to try to store the values into some sort of variable, but I don't know how to do that outside of the pg command line tool.

What I'm after is something analogous to

top_id_one = insert into top(null, 'first_top') returning top_id
mid_id_one = insert into mid(null, top_id_one, 'first_mid_for_first_top) returning mid_id
insert into bot(null, mid_id_one, 'first_bot_for_first_mid')

in a "pure sql" that I could paste into PG admin for example.

I realise that there's a few problems in the example above (for example using 'top' as a table name). My explorations have avoided these issues, but I was trying to find table names that would make my example make sense.

Upvotes: 2

Views: 1597

Answers (2)

klin
klin

Reputation: 121784

You can choose which mid_id use to insert values into bot by numbering rows inserted into mid. Use these row numbers (rn) to identify values inserted into bot:

with top_ins as (
    insert into top (top_name)
    values ('first_top')
    returning top_id
    ),
mid_ins as (
    insert into mid (top_id, mid_name)
    select top_id, name
    from (
        values 
            ('first_mid'), 
            ('second_mid')
        ) v(name)
    cross join top_ins
    returning mid_id
    ),
mid_ins_rn as (
    select mid_id, row_number() over() rn
    from mid_ins
    order by 1
    )
insert into bot (mid_id, bot_name)
select mid_id, name
from (
    values 
        (1, 'first_bot_for_first_mid'), 
        (1, 'second_bot_for_first_mid'),
        (2, 'first_bot_for_second_mid')
    ) v(rn, name)
join mid_ins_rn
using (rn);

Results: 

select * from top;

 top_id | top_name  
--------+-----------
      1 | first_top
(1 row)

select * from mid;

 mid_id | top_id |  mid_name  
--------+--------+------------
      1 |      1 | first_mid
      2 |      1 | second_mid
(2 rows)

select * from bot;

 bot_id | mid_id |         bot_name         
--------+--------+--------------------------
      1 |      1 | first_bot_for_first_mid
      2 |      1 | second_bot_for_first_mid
      3 |      2 | first_bot_for_second_mid
(3 rows)

Upvotes: 3

Benjamin Bau
Benjamin Bau

Reputation: 438

If you can run the sql statements in order, you can use the current value of the sequence (currval that pops the id of the last insert in the table) to get what the value for your top, mid, bot was:

insert into top(top_name) values ('first_top');
insert into mid(top_id, mid_name) select currval('top_top_id_seq'), 'first_mid_for_first_top';
insert into bot(mid_id, bot_name) select currval('mid_mid_id_seq'), 'first_bot_for_first_mid';
insert into bot(mid_id, bot_name) select currval('mid_mid_id_seq'), 'second_bot_for_first_mid';
insert into mid(top_id, mid_name) select currval('top_top_id_seq'), 'second_mid_for_first_top';
insert into top(top_name) values ('second_top');
insert into mid(top_id, mid_name) select currval('top_top_id_seq'), 'first_mid_for_second_top';
insert into bot(mid_id, bot_name) select currval('mid_mid_id_seq'), 'first_bot_for_thir_mid';

This should give the exact result, you showed in your example for a final result.

Upvotes: 1

Related Questions