How can I insert the data from import tables to the model tables in sql?

Question

I'm trying to translate the information from the following model I made into code:

The code I made consists of the creation of the import tables and the insertion of the data into them using 'COPY'. The insertion is succesfull. The issue I have is that I've been trying for a really long time to insert the data from the import table into the tables of the model unsuccesfully while relating the tables to each other. I don't really know how to relate the tables using foreign keys and taking into account the cardinality of each relation (1:1, 1:N or N:M). I've been trying it for hours.

I'd really appreciate if someone could type some generic example on how to do this types of inserts. The following shows the creation of the tables and the copy of the data into the import tables:

drop table if exists ImportA;
create table ImportA (

    id varchar(255),
    url varchar(255),
    name varchar(255),
    description text,
    picture_url varchar(255),
    street varchar(255),
    neighbourhood varchar(255),
    city varchar(255),
    state varchar(255),
    zipcode varchar(255),
    country_code varchar(255),
    country varchar(255),
    property_type varchar(255),
    accommodates varchar(255),
    bathrooms varchar(255),
    bedrooms varchar(255),
    beds varchar(255),
    amenities text,
    square_feet varchar(255),
    price varchar(255),
    weekly_price varchar(255),
    monthly_price varchar(255),
    security_deposit varchar(255),
    cleaning_fee varchar(255),
    minimum_nights varchar(255),
    maximum_nights varchar(255)
);

drop table if exists ImportH;
create table ImportH (

    url varchar(255),
    name varchar(255),
    description text,
    picture_url varchar(255),
    host_id  varchar(255),
    host_url varchar(255),
    host_name varchar(255),
    host_since varchar(255),
    host_about text,
    host_response_time varchar(255),
    host_response_rate varchar(255),
    host_is_superhost varchar(255),
    host_picture_url varchar(255),
    host_listings_count varchar(255),
    host_verifications varchar(255),
    host_identity_verified varchar(255)
);

drop table if exists ImportR;
create table ImportR (

    id varchar(255),
    url varchar(255),
    name varchar(255),
    description text,
    picture_url varchar(255),
    street varchar(255),
    neighbourhood varchar(255),
    city varchar(255),
    date_review varchar(255),
    reviewer_id varchar(255),
    reviewer_name varchar(255),
    comments text
);

copy ImportA from 'C:\BDD\apartments.csv' csv header delimiter ',';
copy ImportH from 'C:\BDD\hosts.csv' csv header delimiter ',';
copy ImportR from 'C:\BDD\review.csv' csv header delimiter ',';

drop table if exists Country cascade;
create table Country (

    country_code varchar(255),
    country varchar(255),
    primary key (country_code)
);

drop table if exists Payment cascade;
create table Payment (

    id_payment serial,
    price varchar(255),
    weekly_price varchar(255),
    monthly_price varchar(255),
    security_deposit varchar(255),
    cleaning_fee varchar(255),
    primary key (id_payment)
);

drop table if exists Characteristic cascade;
create table Characteristic (

    id_characteristic serial,
    property_type varchar(255),
    accommodates int,
    bathrooms real,
    bedrooms int,
    beds int,
    square_feet int,
    minimum_nights int,
    maximum_nights int,
    primary key (id_characteristic)
);

drop table if exists Host cascade;
create table Host (

    host_id serial,
    host_url varchar(255),
    host_name varchar(255),
    host_since date,
    host_about text,
    host_response_time varchar(255),
    host_response_rate varchar(255),
    host_is_superhost boolean,
    host_picture_url varchar(255),
    host_listings_count int,
    host_identity_verified boolean,
    primary key (host_id)
);

drop table if exists Media cascade;
create table Media (

    id_verification serial,
    media varchar(255),
    primary key (id_verification)
);

drop table if exists Reviewer cascade;
create table Reviewer (

    id_reviewer serial,
    reviewer_name varchar(255),
    primary key (id_reviewer)
);

drop table if exists RelationHostMedia cascade;
create table RelationHostMedia (

    host_id int,
    id_verification int,
    primary key (host_id, id_verification),
    foreign key (host_id) references Host (host_id),
    foreign key (id_verification) references Media (id_verification)
);

drop table if exists Amenitie cascade;
create table Amenitie (

    id_amenitie serial,
    amenitie varchar(255),
    primary key (id_amenitie)
);

drop table if exists RelationCharacteristicAmenitie cascade;
create table RelationCharacteristicAmenitie (

    id_amenitie int,
    id_characteristic int,
    primary key (id_amenitie, id_characteristic),
    foreign key (id_amenitie) references Amenitie (id_amenitie),
    foreign key (id_characteristic) references Characteristic (id_characteristic)
);

drop table if exists Place cascade;
create table Place (

    id_place serial,
    street varchar(255),
    neighbourhood varchar(255),
    city varchar(255),
    state varchar(255),
    zipcode varchar(255),
    country_code varchar(255),
    primary key (id_place),
    foreign key (country_code) references Country (country_code)
);

drop table if exists Apartment cascade;
create table Apartment (

    id_apartment serial,
    url varchar(255),
    name varchar(255),
    description text,
    picture_url varchar(255),
    id_place int,
    id_payment int,
    id_characteristic int,
    host_id int,
    primary key (id_apartment),
    foreign key (id_place) references Place (id_place),
    foreign key (id_payment) references Payment (id_payment),
    foreign key (id_characteristic) references Characteristic (id_characteristic),
    foreign key (host_id) references Host (host_id)
);

drop table if exists Review cascade;
create table Review (

    id_review serial,
    id_reviewer int,
    id_apartment int,
    date_review date,
    comments text,
    primary key (id_review),
    foreign key (id_reviewer) references Reviewer (id_reviewer),
    foreign key (id_apartment) references Apartment (id_apartment)
);

This is an example I tried (with no success) for a relation N:M from my model:

insert into Characteristic (property_type, accommodates, bathrooms, bedrooms, beds, square_feet, minimum_nights, maximum_nights)
select property_type, cast(accommodates as int), cast(bathrooms as real), cast(bedrooms as int), cast(beds as int), cast(square_feet as int), cast(minimum_nights as int), cast(maximum_nights as int)
from ImportA;

insert into Amenitie (amenitie)
select distinct regexp_split_to_table(amenities)
from ImportA;

insert into RelationCharacteristicAmenitie (id_amenitie, id_characteristic)
select distinct Amenitie.id_amenitie, Characteristic.id_characteristic
from Amenitie natural join Characteristic;

Thanks in advance.

EDIT

This is the inserts I'm executing:

insert into Country (country_code, country) 
select distinct country_code, country
from ImportA;

insert into Payment (price, weekly_price, monthly_price, security_deposit, cleaning_fee)
select distinct price, weekly_price, monthly_price, security_deposit, cleaning_fee
from ImportA;

insert into Characteristic (property_type, accommodates, bathrooms, bedrooms, beds, square_feet, minimum_nights, maximum_nights)
select property_type, cast(accommodates as int), cast(bathrooms as real), cast(bedrooms as int), cast(beds as int), cast(square_feet as int), cast(minimum_nights as int), cast(maximum_nights as int)
from ImportA;

insert into Amenitie (amenitie)
select distinct regexp_split_to_table(amenities)
from ImportA;

insert into RelationCharacteristicAmenitie (id_amenitie, id_characteristic)
select distinct Amenitie.id_amenitie, Characteristic.id_characteristic
from Amenitie natural join Characteristic;

insert into Host (host_url, host_name, host_since, host_about, host_response_time, host_response_rate, host_is_superhost, host_picture_url, host_listings_count, host_identity_verified)
select distinct host_url, host_name, cast(host_since as date), cast(host_about as text), host_response_time, host_response_rate, cast(host_is_superhost as boolean), host_picture_url, cast(host_listings_count as int), cast(host_identity_verified as boolean)
from ImportH;

insert into Media (media)
select distinct regexp_split_to_table(host_verifications, ',')
from ImportH;

insert into RelationHostMedia (host_id, id_verification)
select Host.host_id, Media.id_verification
from Host natural join Media;

insert into Place (street, neighbourhood, city, state, zipcode, country_code)
select distinct I.street, I.neighbourhood, I.city, I.state, I.zipcode, C.country_code
from ImportA as I, Country as C;

insert into Apartment (url, name, description, picture_url, id_place, id_payment, id_characteristic, host_id)
select distinct I.url, I.name, cast(I.description as text), I.picture_url, P.id_place, Pm.id_payment, C.id_characteristic, H.host_id
from ImportA as I, Place as P, Payment as Pm, Characteristic as C, Host as H;
select * from Apartment;

insert into Reviewer (reviewer_name)
select distinct reviewer_name
from ImportR;

insert into Review (reviewer_id, id_apartment, date_review, comments)
select distinct R.reviewer_id, A.id_apartment, cast(I.date_review as date), cast(I.comments as text)
from Reviewer as R, Apartment as A, ImportR as I;

Answer 1

You have the right idea. Could you post the error you are getting please? The following demonstrates a successful insert-select with postgres, but is clearly a very minimal example, so would be good to step through exactly the issue you have.

postgres=# create table t1 ( id int );
CREATE TABLE
postgres=# create table t2 ( id int );                                          
CREATE TABLE
postgres=# insert into t1 (id) values (1), (2), (3);
INSERT 0 3
postgres=# insert into t2 (id) select id from t1;
INSERT 0 3
postgres=# select * from t2;
 id 
----
  1
  2
  3
(3 rows)