PostgreSQL Inserting Grouped Result Into Two Tables

In this post I will show you how to insert a grouped result from a table into two separate table, into a master table and then into its detail table. For example I will take the below example, where I will create the table ‘temp_detail’ which has the data that should be grouped. First we will create the table ‘temp_detail’ with columns country_id, state_id, state and population using the below query.

    CREATE TABLE temp_detail
(
  id serial primary key not null,
  country_id integer,
  state_id integer,
  state character varying(50),
  population integer
);

Next we will insert the values into the ‘temp_detail’ table. Let us assume country_id ‘1’ is USA and country_id ‘2’ is Canada. We will insert the data using the below query.

insert into temp_detail (country_id,state_id,state,population) values (1,1,'California',39144818);
insert into temp_detail (country_id,state_id,state,population) values (1,2,'Texas',27469114);
insert into temp_detail (country_id,state_id,state,population) values (1,3,'Florida',20271272);

insert into temp_detail (country_id,state_id,state,population) values (2,4,'Ontario',12851821);
insert into temp_detail (country_id,state_id,state,population) values (2,5,'Quebec',7903001);
insert into temp_detail (country_id,state_id,state,population) values (2,6,'British Columbia',4400057);

Next we will create the ‘master’ table for inserting the country_id. We will write the below query to create the table ‘master’.

CREATE TABLE master
(
  master_id serial primary key not null,
  country_id integer
); 

Now we can create the ‘detail’ table to insert the rest of the details that is the state_id, state, population and a reference to the master table. We can write the below query to create the table ‘detail’.

CREATE TABLE detail
(
  id serial primary key not null,
  master_id integer references master (master_id),
  state_id integer,
  state character varying(50),
  population integer
);

Next is the important part, we have to write a query that will group the result based on country_id and then insert into the ‘master’ and ‘detail’ table. We will use the ‘with’ clause in PostgreSQL to write the query. Below is the query which will insert the values into two tables.

 with m as (
    insert into master (country_id)
    select distinct country_id
    from temp_detail
    returning *
)
insert into detail (master_id,state_id, state, population)
select master_id,state_id, state, population
from
    temp_detail t
    inner join
    m on (t.country_id) = (m.country_id);

Now you will be able to see the below data in the ‘master’ and ‘detail’ tables.