Content

Running the following script will generate metrics. When you run this script on your database, it should match the metrics from the database dump called ema.dump that should accompany this document.

WITH tbl AS
  (SELECT table_schema,
          TABLE_NAME
   FROM information_schema.tables
   WHERE TABLE_NAME NOT LIKE 'pg_%'
     AND table_schema IN ('public', 'import'))
SELECT table_schema,
       TABLE_NAME,
       (xpath('/row/c/text()', query_to_xml(
           format(
               'SELECT count(*) as c from %I.%I',
               table_schema, TABLE_NAME),
               FALSE, TRUE, ''))
       )[1]::text::int AS rows_n
FROM tbl
ORDER BY rows_n DESC;

Result of running the script to generate metrics:

table_schematable_namerows_n
importsubstance_reaction45951332
importsubstance_subject_drug_list32594176
importsubstance_concomitant_drug_list21117169
importsubstance13457327
importproduct_reaction10881192
importproduct_subject_drug_list6453406
importproduct_concomitant_drug_list5932744
importproduct3369026
importsubstance_literature1499182
importproduct_literature109405
publicsubstance_cases_per_country30979
publicproduct_cases_per_country11759
importsubstances4333
importproducts1469

Now that we have cleaned all duplicates from all tables, we proceed to look into the data to perform analyses.