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
 ;