postgresql: how to import data from two separate tables into a single table into the SAME row?

I am using postgresql to create countries table from single_sales_records. single_sales_records looks like this:

sales_id |region                           |country| <Many other columns!>
---------------------------------------------------------------------------
1        |Australia and Oceania            |Tuvalu | ...
2        |Central America and the Caribbean|Grenada| ...
3        |Europe                           |Russia | ...
.
.

Before this, I created a regions table, which basically looks like this:

region_id | region
-------------------------------
1         |asia
2         |australia and oceania
3         |central america and the caribbean
4         |north america
5         |sub-saharan africa
6         |middle east and north africa
7         |europe

The countries table (1 region can have many countries) that I want to create is supposed to look like this:

country_id | country | region_id
-----------------------------------------
1          |korea    |1
.
.
.

I have a query that helped me to populate my ‘regions’ table:

INSERT INTO regions (region)
SELECT DISTINCT region
FROM single_sales_records;

And I am having trouble with my current query that is supposed populate by ‘countries’ table:

INSERT INTO countries (country)
SELECT DISTINCT country
FROM single_sales_records;
INSERT INTO countries (region_id)
SELECT DISTINCT region_id
FROM regions;

When I do this, my ‘countries’ table doesn’t match a country that is inside a region – instead it lists all the country, and then add 7 additional lines for the regions. it looks like this:

country_id | country | region_id
-----------------------------------------
1          |korea    |null
.
.
76         |haiti    |null
77         |null     |1
.
.
83         |null     |7

Can someone please help me to match the country with the associated region?

I tried to look for other so articles, but they were talking about inserting data INTO two separate tables, or talking about joins (i don’t think this would exactly require join… right?).

Thank you so much! [This article was edited to include single_sales_records]

Answer

You want to JOIN the tables together for the INSERT:

INSERT INTO countries (region_id, country)
    SELECT DISTINCT r.region_id, country
    FROM single_sales_records ssr JOIN
         regions r
         ON ssr.region = r.region;

Advertisement

Answer