My title is a little misleading, what I am doing is inserting data from a CSV file where each row has information corresponding to three different tables. You can imagine the schema being something like this:
CREATE TABLE runner (id integer PK, name varchar); CREATE TABLE race (id integer PK, name varchar); CREATE TABLE race_result (id integer PK, race_id integer, runner_id ineteger, fp integer);
When I receive the data, it is in a CSV file where each row looks like:
runner_name, race_name, finish_position
Each runner can have multiple race results and so can each race. I am trying to figure out an efficient way to insert this data. I can first insert all runners, then insert all races, then finally insert all race_results and loop up all the ids to fill in the FKs, but this would be awfully slow. Another thing I am trying is:
CREATE TEMP TABLE tmp_x (run_name vc, race_name vc, fp int); COPY tmp_x FROM 'input.csv' (FORMAT csv); WITH run_ins AS ( INSERT INTO runner (name) SELECT run_name FROM tmp_x ON CONFLICT ON some_contraint DO NOTHING RETURNING id ), rac_ins AS ( INSERT INTO race (name) SELECT race_name FROM tmp_x ON CONFLICT ON some_constraint DO NOTHING RETURNING id) INSERT INTO race_result (race_id, runner_id, fp) SELECT rc.id, ru.id, t.fp FROM rac_ins rc, run_ins ru, tmp_x t; DROP TABLE tmp_x;
The problem is that this joins the tables creating more results then there actually are. First, is this the best way to go about it? If it is, can I remedy this by explicitly JOINing the tables in some way? I think my problem is that I do not fully understand what happens when I use the WITH statement. Does it do all of the inserts first then creates a temp table/view that has all of the ids of the records that were inserted? If so, does it return an id twice if I tried to insert but there was a conflict on a constraint?