4-step Postgres CSV Import

1-create-table.sql

-- Create table to hold raw imported data
--
DROP TABLE IF EXISTS imported_reports;
CREATE TABLE imported_reports (
  reference               varchar primary key,
  project_reference       varchar,
  project_name            varchar,
  version                 varchar,
  generation_time         varchar,
  -- etc
);

2-copy-csv.sql

-- Import data
--
COPY imported_reports
FROM '/tmp/large-enterprise-export.csv'
WITH (format csv, header)
;
  

3a-insert-parents.sql

-- Load projects from import
--
INSERT INTO projects (reference, name, created_at, updated_at)
  SELECT
    i.project_reference AS reference,
    i.project_name      AS name
    now()               AS created_at,
    now()               AS updated_at
  FROM (SELECT DISTINCT project_reference, project_name FROM imported_reports) i
ON CONFLICT (reference) DO UPDATE SET
  name          = excluded.name,
  updated_at    = NOW()
;

3b-insert-records.sql

INSERT INTO reports (reference, program_id, generation_time, version)
  SELECT
    i.reference
    programs.id AS program_id,
    nullif(i.generation_time, '')::timestamp AS generation_time,
    nullif(i.version, '')::numeric AS version,
    -- etc
  FROM imported_reports i
  JOIN programs ON programs.reference = i.program_reference
ON CONFLICT (external_id) DO UPDATE SET
  reference       = excluded.reference
  program_id      = excluded.program_id,
  generation_time = excluded.generation_time,
  version         = excluded.version
  -- etc
 ;

4-cleanup.sql

DROP TABLE imported_reports;
VACUUM FULL ANALYZE VERBOSE;