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_schema | table_name | rows_n |
---|---|---|
import | substance_reaction | 45951332 |
import | substance_subject_drug_list | 32594176 |
import | substance_concomitant_drug_list | 21117169 |
import | substance | 13457327 |
import | product_reaction | 10881192 |
import | product_subject_drug_list | 6453406 |
import | product_concomitant_drug_list | 5932744 |
import | product | 3369026 |
import | substance_literature | 1499182 |
import | product_literature | 109405 |
public | substance_cases_per_country | 30979 |
public | product_cases_per_country | 11759 |
import | substances | 4333 |
import | products | 1469 |
Now that we have cleaned all duplicates from all tables, we proceed to look into the data to perform analyses.