Clean

After importing the line listings there are duplicate records in the database in various tables. The following SQL script makes sure all tables in the import schema contained unique reports and supporting tables only.

Remove duplicates from substance

CREATE TABLE import.substance_unique AS
SELECT
  substance_id,
  local_number,
  report_type,
  report_date,
  qualification,
  country,
  age_group_lower_months,
  age_group_upper_months,
  age_group_reporter,
  is_child_report,
  sex,
  serious
FROM import.substance GROUP BY 
  substance_id,
  local_number,
  report_type,
  report_date,
  qualification,
  country,
  age_group_lower_months,
  age_group_upper_months,
  age_group_reporter,
  is_child_report,
  sex,
  serious;
  
DROP TABLE import.substance;

ALTER TABLE import.substance_unique
  RENAME TO substance;

Remove duplicates from substance_concomitant_drug_list

CREATE TABLE import.substance_concomitant_drug_list_unique AS
SELECT
  substance_id,
  local_number,
  report_date,
  drugs,
  characteristic,
  indication,
  action,
  duration,
  dose,
  route
FROM import.substance_concomitant_drug_list GROUP BY 
  substance_id,
  local_number,
  report_date,
  drugs,
  characteristic,
  indication,
  action,
  duration,
  dose,
  route;
DROP TABLE import.substance_concomitant_drug_list;

ALTER TABLE import.substance_concomitant_drug_list_unique
  RENAME TO substance_concomitant_drug_list;

Remove duplicates from substance_subject_drug_list

CREATE TABLE import.substance_subject_drug_list_unique AS
SELECT
  substance_id,
  local_number,
  report_date,
  drugs,
  characteristic,
  indication,
  action,
  duration,
  dose,
  route
FROM import.substance_subject_drug_list GROUP BY 
  substance_id,
  local_number,
  report_date,
  drugs,
  characteristic,
  indication,
  action,
  duration,
  dose,
  route;

DROP TABLE import.substance_subject_drug_list;

ALTER TABLE import.substance_subject_drug_list_unique
  RENAME TO substance_subject_drug_list;

Remove duplicates from substance_reaction

CREATE TABLE import.substance_reaction_unique AS
SELECT
  substance_id,
  local_number,
  report_date,
  reaction,
  duration,
  outcome,
  seriousness_criteria
FROM import.substance_reaction GROUP BY 
  substance_id,
  local_number,
  report_date,
  reaction,
  duration,
  outcome,
  seriousness_criteria;
DROP TABLE import.substance_reaction;
ALTER TABLE import.substance_reaction_unique RENAME TO substance_reaction;

Remove duplicates from substance_literature

CREATE TABLE import.substance_literature_unique AS
SELECT
  substance_id,
  local_number,
  report_date,
  literature
FROM import.substance_literature GROUP BY 
  substance_id,
  local_number,
  report_date,
  literature;
DROP TABLE import.substance_literature;
ALTER TABLE import.substance_literature_unique RENAME TO substance_literature;

Remove duplicates from product

CREATE TABLE import.product_unique AS
SELECT
  product_id,
  local_number,
  report_type,
  report_date,
  qualification,
  country,
  age_group_lower_months,
  age_group_upper_months,
  age_group_reporter,
  is_child_report,
  sex,
  serious
FROM import.product GROUP BY 
  product_id,
  local_number,
  report_type,
  report_date,
  qualification,
  country,
  age_group_lower_months,
  age_group_upper_months,
  age_group_reporter,
  is_child_report,
  sex,
  serious;
 DROP TABLE import.product;
 ALTER TABLE import.product_unique RENAME TO product;

Remove duplicates from product_concomitant_drug_list

CREATE TABLE import.product_concomitant_drug_list_unique AS
SELECT
  product_id,
  local_number,
  report_date,
  drugs,
  characteristic,
  indication,
  action,
  duration,
  dose,
  route
FROM import.product_concomitant_drug_list GROUP BY 
  product_id,
  local_number,
  report_date,
  drugs,
  characteristic,
  indication,
  action,
  duration,
  dose,
  route;
 DROP TABLE import.product_concomitant_drug_list;
 ALTER TABLE import.product_concomitant_drug_list_unique
   RENAME TO product_concomitant_drug_list;

Remove duplicates from product_subject_drug_list

CREATE TABLE import.product_subject_drug_list_unique AS
select
  product_id,
  local_number,
  report_date,
  drugs,
  characteristic,
  indication,
  action,
  duration,
  dose,
  route
FROM import.product_subject_drug_list GROUP BY 
  product_id,
  local_number,
  report_date,
  drugs,
  characteristic,
  indication,
  action,
  duration,
  dose,
  route;
 DROP TABLE import.product_subject_drug_list;
 ALTER TABLE import.product_subject_drug_list_unique
   RENAME TO product_subject_drug_list;

Remove duplicates from product_reaction

CREATE TABLE import.product_reaction_unique AS
SELECT
  product_id,
  local_number,
  report_date,
  reaction,
  duration,
  outcome,
  seriousness_criteria
FROM import.product_reaction GROUP BY 
  product_id,
  local_number,
  report_date,
  reaction,
  duration,
  outcome,
  seriousness_criteria;
 DROP TABLE import.product_reaction;
 ALTER TABLE import.product_reaction_unique RENAME TO product_reaction;

Remove duplicates from product_literature

CREATE TABLE import.product_literature_unique AS
SELECT
  product_id,
  local_number,
  report_date,
  literature
FROM import.product_literature GROUP BY 
  product_id,
  local_number,
  report_date,
  literature;
DROP TABLE import.product_literature;
ALTER TABLE import.product_literature_unique RENAME TO product_literature;