DROP TABLE IF EXISTS soc;
DROP TABLE IF EXISTS pt;
DROP TABLE IF EXISTS llt;

CREATE TABLE soc (
	soc_code BIGINT constraint ix1_soc01 PRIMARY KEY,
	soc_name varchar(100) constraint ix1_soc02 NOT NULL,
	soc_abbrev varchar(5) NOT NULL,
	soc_whoart_code varchar(7),
	soc_harts_code BIGINT,
	soc_costart_sym varchar(21),
	soc_icd9_code varchar(8),
	soc_icd9cm_code varchar(8),
	soc_icd10_code varchar(8),
	soc_jart_code varchar(8),
	x varchar(1)
);

CREATE TABLE pt (
	pt_code BIGINT constraint ix1_pt01 PRIMARY KEY,
	pt_name varchar(100) constraint ix1_pt02 NOT NULL,
	null_field varchar(1),
	pt_soc_code BIGINT,
	pt_whoart_code varchar(7),
	pt_harts_code BIGINT,
	pt_costart_sym varchar(21),
	pt_icd9_code varchar(8),
	pt_icd9cm_code varchar(8),
	pt_icd10_code varchar(8),
	pt_jart_code varchar(6),
	x varchar(1)
);

CREATE TABLE llt (
	llt_code BIGINT constraint ix1_pt_llt01 PRIMARY KEY,
	llt_name varchar(100) constraint ix1_pt_llt02 NOT NULL,
	pt_code BIGINT,
	llt_whoart_code varchar(7),
	llt_harts_code BIGINT,
	llt_costart_sym varchar(21),
	llt_icd9_code varchar(8),
	llt_icd9cm_code varchar(8),
	llt_icd10_code varchar(8),
	llt_currency varchar(1),
	llt_jart_code varchar(6),
	x varchar(1)
);

COPY soc
FROM '/Users/neil/Documents/MMM/MedDRA_15_0_English/MedAscii/soc.asc'
WITH
	DELIMITER AS '$'
	NULL AS '';

COPY pt
FROM '/Users/neil/Documents/MMM/MedDRA_15_0_English/MedAscii/pt.asc'
WITH
	DELIMITER AS '$'
	NULL AS '';

COPY llt
FROM '/Users/neil/Documents/MMM/MedDRA_15_0_English/MedAscii/llt.asc'
WITH
	DELIMITER AS '$'
	NULL AS '';

ALTER TABLE soc DROP COLUMN x;
ALTER TABLE pt DROP COLUMN x;
ALTER TABLE llt DROP COLUMN x;