create_tables.sql

SET search_path TO mimiciv;

DROP TABLE IF EXISTS ADMISSIONS CASCADE;
CREATE TABLE ADMISSIONS
(
	SUBJECT_ID INT NOT NULL,
	HADM_ID INT NOT NULL,
	ADMITTIME TIMESTAMP(0) NOT NULL,
	DISCHTIME TIMESTAMP(0) NOT NULL,
	DEATHTIME TIMESTAMP(0),
	ADMISSION_TYPE VARCHAR(50) NOT NULL,
	ADMISSION_LOCATION VARCHAR(50), -- There is NULL in this version
	DISCHARGE_LOCATION VARCHAR(50), -- There is NULL in this version
	INSURANCE VARCHAR(255) NOT NULL,
	LANGUAGE VARCHAR(10),
	MARITAL_STATUS VARCHAR(50),
	ETHNICITY VARCHAR(200) NOT NULL,
	EDREGTIME TIMESTAMP(0),
	EDOUTTIME TIMESTAMP(0),
	HOSPITAL_EXPIRE_FLAG SMALLINT,
	CONSTRAINT adm_hadm_pk PRIMARY KEY (HADM_ID),
	CONSTRAINT adm_hadm_unique UNIQUE (HADM_ID)

);



DROP TABLE IF EXISTS PATIENTS CASCADE;
CREATE TABLE PATIENTS
(
	SUBJECT_ID INT NOT NULL,
	GENDER VARCHAR(5) NOT NULL,
	ANCHOR_AGE INT NOT NULL,
	ANCHOR_YEAR INT NOT NULL,
	ANCHOR_YEAR_GROUP VARCHAR(12) NOT NULL,
	DOD TIMESTAMP(0), -- This is a NaN column

	CONSTRAINT pat_subid_unique UNIQUE (SUBJECT_ID),
	CONSTRAINT pat_subid_pk PRIMARY KEY (SUBJECT_ID)
) ;



DROP TABLE IF EXISTS TRANSFERS CASCADE;
CREATE TABLE TRANSFERS
(
	-- There is no stay_id in this table
	-- The subject_id and transfer_id are able to serve as PK on check
	-- Some careunit exceeds the length of 20 in mimic iv
	SUBJECT_ID INT NOT NULL,
	HADM_ID INT,
	TRANSFER_ID INT NOT NULL,
	EVENTTYPE VARCHAR(20) NOT NULL,
	CAREUNIT VARCHAR(50),
	INTIME TIMESTAMP(0),
	OUTTIME TIMESTAMP(0),
	CONSTRAINT transfers_subid_transid_pk PRIMARY KEY (SUBJECT_ID,TRANSFER_ID) 

) ;

DROP TABLE IF EXISTS D_ITEMS CASCADE;
CREATE TABLE D_ITEMS
(
	ITEMID INT NOT NULL,
	LABEL VARCHAR(200) NOT NULL,
	ABBREVIATION VARCHAR(100) NOT NULL,
	LINKSTO VARCHAR(50) NOT NULL,
	CATEGORY VARCHAR(100) NOT NULL,
	UNITNAME VARCHAR(100),
	PARAM_TYPE VARCHAR(30) NOT NULL,
	LOWNORMALVALUE DOUBLE PRECISION,
	HIGHNORMALVALUE DOUBLE PRECISION,
	CONSTRAINT ditems_itemid_unique UNIQUE (ITEMID),
	CONSTRAINT ditems_itemid_pk PRIMARY KEY (ITEMID)
) ;

DROP TABLE IF EXISTS DATETIMEEVENTS CASCADE;
CREATE TABLE DATETIMEEVENTS
(
	SUBJECT_ID INT NOT NULL,
	HADM_ID INT,
	STAY_ID INT,
	CHARTTIME TIMESTAMP(0) NOT NULL,
	STORETIME TIMESTAMP(0) NOT NULL,
	ITEMID INT NOT NULL,
	VALUE TIMESTAMP(0) NOT NULL,
	VALUEUOM VARCHAR(50) NOT NULL,
	WARNING SMALLINT NOT NULL
) ;

DROP TABLE IF EXISTS ICUSTAYS CASCADE;
CREATE TABLE ICUSTAYS
(
	-- Maximum length of CAREUNIT exceeds 20 in mimic iv
	SUBJECT_ID INT NOT NULL,
	HADM_ID INT NOT NULL,
	STAY_ID INT NOT NULL,
	FIRST_CAREUNIT VARCHAR(50) NOT NULL,
	LAST_CAREUNIT VARCHAR(50) NOT NULL,
	INTIME TIMESTAMP(0) NOT NULL,
	OUTTIME TIMESTAMP(0),
	LOS DOUBLE PRECISION,
	CONSTRAINT icustay_stayid_unique UNIQUE (STAY_ID),
	CONSTRAINT icustay_stayid_unique PRIMARY KEY (STAY_ID)
) ;


DROP TABLE IF EXISTS INPUTEVENTS CASCADE;
CREATE TABLE INPUTEVENTS
(
	-- Actually No ROW_ID
	SUBJECT_ID INT NOT NULL,
	HADM_ID INT NOT NULL,
	STAY_ID INT NOT NULL,
	STARTTIME TIMESTAMP(0) NOT NULL,
	ENDTIME TIMESTAMP(0) NOT NULL,
	STORETIME TIMESTAMP(0) NOT NULL,
	ITEMID INT NOT NULL,
	AMOUNT DOUBLE PRECISION NOT NULL,
	AMOUNTUOM VARCHAR(30) NOT NULL,
	RATE DOUBLE PRECISION,
	RATEUOM VARCHAR(30),
	ORDERID INT NOT NULL,
	LINKORDERID INT NOT NULL,
	ORDERCATEGORYNAME VARCHAR(100) NOT NULL,
	SECONDARYORDERCATEGORYNAME VARCHAR(100),
	ORDERCOMPONENTTYPEDESCRIPTION VARCHAR(200) NOT NULL,
	ORDERCATEGORYDESCRIPTION VARCHAR(50) NOT NULL,
	PATIENTWEIGHT DOUBLE PRECISION NOT NULL,
	TOTALAMOUNT DOUBLE PRECISION,
	TOTALAMOUNTUOM VARCHAR(50),
	ISOPENBAG SMALLINT NOT NULL,
	CONTINUEINNEXTDEPT SMALLINT NOT NULL,
	CANCELREASON SMALLINT NOT NULL,
	STATUSDESCRIPTION VARCHAR(30) NOT NULL,
	ORIGINALAMOUNT DOUBLE PRECISION NOT NULL,
	ORIGINALRATE DOUBLE PRECISION NOT NULL
) ;


DROP TABLE IF EXISTS OUTPUTEVENTS CASCADE;
CREATE TABLE OUTPUTEVENTS
(
	SUBJECT_ID INT NOT NULL,
	HADM_ID INT NOT NULL,
	STAY_ID INT NOT NULL,
	CHARTTIME TIMESTAMP(0) NOT NULL,
	STORETIME TIMESTAMP(0) NOT NULL,
	ITEMID INT NOT NULL,
	VALUE DOUBLE PRECISION NOT NULL,
	VALUEUOM VARCHAR(30) NOT NULL
) ;


DROP TABLE IF EXISTS PROCEDUREEVENTS CASCADE;
CREATE TABLE PROCEDUREEVENTS
(
	SUBJECT_ID INT NOT NULL,
	HADM_ID INT NOT NULL,
	STAY_ID INT NOT NULL,
	STARTTIME TIMESTAMP(0) NOT NULL,
	ENDTIME TIMESTAMP(0) NOT NULL,
	STORETIME TIMESTAMP(0) NOT NULL,
	ITEMID INT NOT NULL,
	VALUE DOUBLE PRECISION NOT NULL,
	VALUEUOM VARCHAR(30) NOT NULL,
	LOCATION VARCHAR(30),
	LOCATIONCATEGORY VARCHAR(30),
	ORDERID INT NOT NULL,
	LINKORDERID INT NOT NULL,
	ORDERCATEGORYNAME VARCHAR(100) NOT NULL,
	SECONDARYORDERCATEGORYNAME VARCHAR(100),
	ORDERCATEGORYDESCRIPTION VARCHAR(50) NOT NULL,
	PATIENTWEIGHT DOUBLE PRECISION NOT NULL,
	TOTALAMOUNT DOUBLE PRECISION,
	TOTALAMOUNTUOM VARCHAR(50),
	ISOPENBAG SMALLINT NOT NULL,
	CONTINUEINNEXTDEPT SMALLINT NOT NULL,
	CANCELREASON SMALLINT NOT NULL,
	STATUSDESCRIPTION VARCHAR(30) NOT NULL,
	COMMENTS_DATE TIMESTAMP(0),
	ORIGINALAMOUNT DOUBLE PRECISION NOT NULL,
	ORIGINALRATE DOUBLE PRECISION NOT NULL
) ;



DROP TABLE IF EXISTS D_HCPCS CASCADE;
CREATE TABLE D_HCPCS
(
	CODE VARCHAR(5) NOT NULL,
	CATEGORY SMALLINT,
	LONG_DESCRIPTION TEXT,
	SHORT_DESCRIPTION VARCHAR(180) NOT NULL,
	CONSTRAINT d_hcpcs_code_unique UNIQUE (CODE),
	CONSTRAINT d_hcpcs_code_pk PRIMARY KEY (CODE)

) ;

DROP TABLE IF EXISTS D_ICD_DIAGNOSES CASCADE;
CREATE TABLE D_ICD_DIAGNOSES
(
	ICD_CODE VARCHAR(10) NOT NULL,
	ICD_VERSION INT NOT NULL,
	LONG_TITLE VARCHAR(300) NOT NULL,
	CONSTRAINT d_icd_diagonses_icd_code_version_pk PRIMARY KEY (ICD_CODE,ICD_VERSION)

) ;

DROP TABLE IF EXISTS D_ICD_PROCEDURES CASCADE;
CREATE TABLE D_ICD_PROCEDURES
(
	ICD_CODE VARCHAR(10) NOT NULL,
	ICD_VERSION INT NOT NULL,
	LONG_TITLE VARCHAR(300) NOT NULL,
	CONSTRAINT d_icd_procedures_icd_code_pk PRIMARY KEY (ICD_CODE,ICD_VERSION)

) ;

DROP TABLE IF EXISTS D_LABITEMS CASCADE;
CREATE TABLE D_LABITEMS
(
	ITEMID INT NOT NULL,
	LABEL VARCHAR(50),
	FLUID VARCHAR(50) NOT NULL,
	CATEGORY VARCHAR(50) NOT NULL,
	LOINC_CODE VARCHAR(50),
	CONSTRAINT d_labitems_itemid_pk PRIMARY KEY (ITEMID)

) ;

DROP TABLE IF EXISTS DIAGNOSES_ICD CASCADE;
CREATE TABLE DIAGNOSES_ICD
(
	SUBJECT_ID INT NOT NULL,
	HADM_ID INT NOT NULL,
	SEQ_NUM INT NOT NULL,
	ICD_CODE VARCHAR(10) NOT NULL,
	ICD_VERSION INT NOT NULL
) ;

DROP TABLE IF EXISTS DRGCODES CASCADE;
CREATE TABLE DRGCODES
(
	SUBJECT_ID INT NOT NULL,
	HADM_ID INT NOT NULL,
	DRG_TYPE VARCHAR(4) NOT NULL,
	DRG_CODE VARCHAR(10) NOT NULL,
	DESCRIPTION VARCHAR(195) ,
	DRG_SEVERITY SMALLINT,
	DRG_MORTALITY SMALLINT
) ;

DROP TABLE IF EXISTS EMAR CASCADE;
CREATE TABLE EMAR
(
	-- There is NULL in HADM_ID
	-- The official description ignores pharmacy_id
	SUBJECT_ID INT NOT NULL,
	HADM_ID INT ,
	EMAR_ID VARCHAR(100) NOT NULL,
	EMAR_SEQ INT NOT NULL,
	POE_ID VARCHAR(25) NOT NULL,
	PHARMACY_ID INT ,
	CHARTTIME TIMESTAMP(0) NOT NULL,
	MEDICATION TEXT ,
	EVENT_TXT TEXT ,
	SCHEDULETIME TIMESTAMP(0) ,
	STORETIME TIMESTAMP(0) NOT NULL,
	CONSTRAINT emar_emar_id_pk PRIMARY KEY (EMAR_ID)
) ;


DROP TABLE IF EXISTS EMAR_DETAIL CASCADE;
CREATE TABLE EMAR_DETAIL
(
	SUBJECT_ID INT NOT NULL,
	EMAR_ID VARCHAR(25) NOT NULL,
	EMAR_SEQ INT NOT NULL,
	PARENT_FIELD_ORDINAL DOUBLE PRECISION ,
	ADMINISTRATION_TYPE VARCHAR(50) ,
	PHARMACY_ID INT ,
	BARCODE_TYPE VARCHAR(4) ,
	REASON_FOR_NO_BARCODE TEXT ,
	COMPLETE_DOSE_NOT_GIVEN  VARCHAR(5),
	DOSE_DUE VARCHAR(55) ,
	DOSE_DUE_UNIT VARCHAR(30) ,
	DOSE_GIVEN VARCHAR(155) ,
	DOSE_GIVEN_UNIT VARCHAR(30) ,
	WILL_REMAINDER_OF_DOSE_BE_GIVEN VARCHAR(5),
	PRODUCT_AMOUNT_GIVEN VARCHAR(30) ,
	PRODUCT_UNIT VARCHAR(30) ,
	PRODUCT_CODE VARCHAR(30) ,
	PRODUCT_DESCRIPTION VARCHAR(200) ,
	PRODUCT_DESCRIPTION_OTHER VARCHAR(100),
	PRIOR_INFUSION_RATE VARCHAR(25),
	INFUSION_RATE VARCHAR(25) ,
	INFUSION_RATE_ADJUSTMENT VARCHAR(40) ,
	INFUSION_RATE_ADJUSTMENT_AMOUNT VARCHAR(30) ,
	INFUSION_RATE_UNIT VARCHAR(30) ,
	ROUTE VARCHAR(10) ,
	INFUSION_COMPLETE VARCHAR(10) ,
	COMPLETION_INTERVAL VARCHAR(30) ,
	NEW_IV_BAG_HUNG VARCHAR(5) ,
	CONTINUED_INFUSION_IN_OTHER_LOCATION VARCHAR(5) ,
	RESTART_INTERVAL VARCHAR(30) ,
	SIDE VARCHAR(10) ,
	SITE VARCHAR(250) ,
	NON_FORMULARY_VISUAL_VERIFICATION VARCHAR(5)
);

DROP TABLE IF EXISTS HCPCSEVENTS CASCADE;
CREATE TABLE HCPCSEVENTS
(
	SUBJECT_ID INT NOT NULL,
	HADM_ID INT NOT NULL,
	CHARTDATE TIMESTAMP(0) NOT NULL, -- new for 1.0
	HCPCS_CD VARCHAR(5) NOT NULL,
	SEQ_NUM INT NOT NULL,
	SHORT_DESCRIPTION VARCHAR(170) NOT NULL
	-- longest is 165

) ;

-- labevents



DROP TABLE IF EXISTS MICROBIOLOGYEVENTS CASCADE;
CREATE TABLE MICROBIOLOGYEVENTS
(
	-- the official description is inconsistent with data
	-- The comments column actually only carries nan or space
	MICROEVENT_ID INT NOT NULL,
	SUBJECT_ID INT NOT NULL,
	HADM_ID INT ,
	MICRO_SPECIMEN_ID INT NOT NULL,
	CHARTDATE TIMESTAMP(0) NOT NULL,
	CHARTTIME TIMESTAMP(0) ,
	SPEC_ITEMID INT NOT NULL,
	SPEC_TYPE_DESC VARCHAR(100) NOT NULL,
	TEST_SEQ INT NOT NULL,
	STOREDATE TIMESTAMP(0) ,
	STORETIME TIMESTAMP(0) ,
	TEST_ITEMID INT NOT NULL,
	TEST_NAME VARCHAR(100) NOT NULL,
	ORG_ITEMID INT ,
	ORG_NAME VARCHAR(100) ,
	ISOLATE_NUM SMALLINT ,
	QUANTITY VARCHAR(50) ,
	AB_ITEMID INT ,
	AB_NAME VARCHAR(30) ,
	DILUTION_TEXT VARCHAR(10) ,
	DILUTION_COMPARISON VARCHAR(20) ,
	DILUTION_VALUE DOUBLE PRECISION ,
	INTERPRETATION VARCHAR(5) ,
	COMMENTS VARCHAR(750),
	CONSTRAINT mbe_microevent_id_pk PRIMARY KEY (MICROEVENT_ID)
) ;


DROP TABLE IF EXISTS PHARMACY CASCADE;
CREATE TABLE PHARMACY
(
	
	SUBJECT_ID INT NOT NULL,
	HADM_ID INT NOT NULL,
	PHARMACY_ID INT NOT NULL,
	POE_ID VARCHAR(25) ,
	STARTTIME TIMESTAMP(0) ,
	STOPTIME TIMESTAMP(0) ,
	MEDICATION VARCHAR(100) ,
	PROC_TYPE VARCHAR(50) NOT NULL,
	STATUS VARCHAR(50) NOT NULL,
	ENTERTIME TIMESTAMP(0) NOT NULL,
	VERIFIEDTIME TIMESTAMP(0) ,
	ROUTE VARCHAR(30),
	FREQUENCY VARCHAR(30) ,
	DISP_SCHED VARCHAR(100) ,
	INFUSION_TYPE VARCHAR(15) ,
	SLIDING_SCALE VARCHAR(5) ,
	LOCKOUT_INTERVAL VARCHAR(50) ,
	BASAL_RATE  DOUBLE PRECISION,
	ONE_HR_MAX  VARCHAR(30), 
	-- has some dirty data that can't be format to double precision
	DOSES_PER_24_HRS DOUBLE PRECISION ,
	DURATION DOUBLE PRECISION,
	DURATION_INTERVAL VARCHAR(50) ,
	EXPIRATION_VALUE INT ,
	EXPIRATION_UNIT VARCHAR(50) ,
	EXPIRATIONDATE TIMESTAMP(0) ,
	DISPENSATION VARCHAR(50) ,
	FILL_QUANTITY VARCHAR(30),
	CONSTRAINT pharmacy_pharmacy_pk PRIMARY KEY (PHARMACY_ID)
) ;

DROP TABLE IF EXISTS POE CASCADE;
CREATE TABLE POE
(
	POE_ID VARCHAR(25) NOT NULL,
	POE_SEQ INT NOT NULL,
	SUBJECT_ID INT NOT NULL,
	HADM_ID INT NOT NULL,
	ORDERTIME TIMESTAMP(0) NOT NULL,
	ORDER_TYPE VARCHAR(25) NOT NULL,
	ORDER_SUBTYPE VARCHAR(50) ,
	TRANSACTION_TYPE VARCHAR(15) NOT NULL,
	DISCONTINUE_OF_POE_ID VARCHAR(25) ,
	DISCONTINUED_BY_POE_ID VARCHAR(25) ,
	ORDER_STATUS VARCHAR(15) ,
	CONSTRAINT poe_poe_id_pk PRIMARY KEY (POE_ID)
) ;


DROP TABLE IF EXISTS POE_DETAIL CASCADE;
CREATE TABLE POE_DETAIL
(
	-- length of field name should be a typo, correct it to 25
	-- Maximum length of field_value is 55
	POE_ID VARCHAR(25) NOT NULL,
	POE_SEQ INT NOT NULL,
	SUBJECT_ID INT NOT NULL,
	FIELD_NAME VARCHAR(25) NOT NULL,
	FIELD_VALUE VARCHAR(60) NOT NULL
) ;

DROP TABLE IF EXISTS PRESCRIPTIONS CASCADE;
CREATE TABLE PRESCRIPTIONS
(
	SUBJECT_ID INT NOT NULL,
	HADM_ID INT NOT NULL,
	PHARMACY_ID INT NOT NULL,
	STARTTIME TIMESTAMP(0) ,
	STOPTIME TIMESTAMP(0) ,
	DRUG_TYPE VARCHAR(10) NOT NULL,
	DRUG VARCHAR(100) ,
	GSN VARCHAR(250) , -- exceeds 10
	NDC VARCHAR(20) ,
	PROD_STRENGTH VARCHAR(120) ,
	FORM_RX  VARCHAR(10),
	DOSE_VAL_RX VARCHAR(50) ,
	DOSE_UNIT_RX VARCHAR(50) ,
	FORM_VAL_DISP VARCHAR(30) ,
	FORM_UNIT_DISP VARCHAR(30) ,
	DOSES_PER_24_HRS DOUBLE PRECISION ,
	ROUTE VARCHAR(30)

) ;

DROP TABLE IF EXISTS PROCEDURES_ICD CASCADE;
CREATE TABLE PROCEDURES_ICD
(
	SUBJECT_ID INT NOT NULL,
	HADM_ID INT NOT NULL,
	SEQ_NUM INT NOT NULL,
	CHARTDATE TIMESTAMP(0) NOT NULL,
	ICD_CODE VARCHAR(10) NOT NULL,
	ICD_VERSION INT NOT NULL
) ;

DROP TABLE IF EXISTS SERVICES CASCADE;
CREATE TABLE SERVICES
(
	SUBJECT_ID INT NOT NULL,
	HADM_ID INT NOT NULL,
	TRANSFERTIME TIMESTAMP(0) NOT NULL,
	PREV_SERVICE VARCHAR(20) ,
	CURR_SERVICE VARCHAR(20) NOT NULL
) ;


DROP TABLE IF EXISTS LABEVENTS CASCADE;
CREATE TABLE LABEVENTS
(
	LABEVENT_ID INT NOT NULL,
	SUBJECT_ID INT NOT NULL,
	HADM_ID INT ,
	SPECIMEN_ID INT NOT NULL,
	ITEMID INT NOT NULL,
	CHARTTIME TIMESTAMP NOT NULL,
	STORETIME TIMESTAMP ,
	VALUE VARCHAR(200) ,
	VALUENUM DOUBLE PRECISION ,
	VALUEUOM VARCHAR(20) ,
	REF_RANGE_LOWER DOUBLE PRECISION ,
	REF_RANGE_UPPER  DOUBLE PRECISION,
	FLAG VARCHAR(10) ,
	PRIORITY VARCHAR(7) ,
	COMMENTS VARCHAR(620) ,
	CONSTRAINT labevents_labeventid_pk PRIMARY KEY (LABEVENT_ID)
) ;


CREATE TABLE labevents_1 ( CHECK ( labevent_id >= 0 AND labevent_id < 12500000 )) INHERITS (labevents);
CREATE TABLE labevents_2 ( CHECK ( labevent_id >= 12500000 AND labevent_id < 25000000 )) INHERITS (labevents);
CREATE TABLE labevents_3 ( CHECK ( labevent_id >= 25000000 AND labevent_id < 37500000 )) INHERITS (labevents);
CREATE TABLE labevents_4 ( CHECK ( labevent_id >= 37500000 AND labevent_id < 50000000 )) INHERITS (labevents);
CREATE TABLE labevents_5 ( CHECK ( labevent_id >= 50000000 AND labevent_id < 62500000 )) INHERITS (labevents);
CREATE TABLE labevents_6 ( CHECK ( labevent_id >= 62500000 AND labevent_id < 75000000 )) INHERITS (labevents);
CREATE TABLE labevents_7 ( CHECK ( labevent_id >= 75000000 AND labevent_id < 87500000 )) INHERITS (labevents);
CREATE TABLE labevents_8 ( CHECK ( labevent_id >= 87500000 AND labevent_id < 100000000 )) INHERITS (labevents);
CREATE TABLE labevents_9 ( CHECK ( labevent_id >= 100000000 AND labevent_id < 112500000 )) INHERITS (labevents);
CREATE TABLE labevents_10 ( CHECK ( labevent_id >= 112500000 AND labevent_id < 125000000 )) INHERITS (labevents);



CREATE OR REPLACE FUNCTION labevents_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF ( NEW.labevent_id >= 0 AND NEW.labevent_id < 12500000 ) THEN INSERT INTO labevents_1 VALUES (NEW.*);
ELSIF ( NEW.labevent_id >= 12500000 AND NEW.labevent_id < 25000000 ) THEN INSERT INTO labevents_2 VALUES (NEW.*);
ELSIF ( NEW.labevent_id >= 25000000 AND NEW.labevent_id < 37500000 ) THEN INSERT INTO labevents_3 VALUES (NEW.*);
ELSIF ( NEW.labevent_id >= 37500000 AND NEW.labevent_id < 50000000 ) THEN INSERT INTO labevents_4 VALUES (NEW.*);
ELSIF ( NEW.labevent_id >= 50000000 AND NEW.labevent_id < 62500000 ) THEN INSERT INTO labevents_5 VALUES (NEW.*);
ELSIF ( NEW.labevent_id >= 62500000 AND NEW.labevent_id < 75000000 ) THEN INSERT INTO labevents_6 VALUES (NEW.*);
ELSIF ( NEW.labevent_id >= 75000000 AND NEW.labevent_id < 87500000 ) THEN INSERT INTO labevents_7 VALUES (NEW.*);
ELSIF ( NEW.labevent_id >= 87500000 AND NEW.labevent_id < 100000000 ) THEN INSERT INTO labevents_8 VALUES (NEW.*);
ELSIF ( NEW.labevent_id >= 100000000 AND NEW.labevent_id < 112500000 ) THEN INSERT INTO labevents_9 VALUES (NEW.*);
ELSIF ( NEW.labevent_id >= 112500000 AND NEW.labevent_id < 125000000 ) THEN INSERT INTO labevents_10 VALUES (NEW.*);
ELSE
	INSERT INTO labevents_null VALUES (NEW.*);
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER insert_labevents_trigger
    BEFORE INSERT ON labevents
    FOR EACH ROW EXECUTE PROCEDURE labevents_insert_trigger();
	
DROP TABLE IF EXISTS CHARTEVENTS CASCADE;
CREATE TABLE CHARTEVENTS
(
	SUBJECT_ID INT NOT NULL,
	HADM_ID INT NOT NULL,
	STAY_ID INT NOT NULL,
	CHARTTIME TIMESTAMP(0) NOT NULL,
	STORETIME TIMESTAMP(0) ,
	ITEMID INT NOT NULL,
	VALUE VARCHAR(160) ,
	VALUENUM DOUBLE PRECISION,
	VALUEUOM VARCHAR(20),
	WARNING SMALLINT NOT NULL
) ;


CREATE TABLE chartevents_1 ( CHECK ( itemid >= 220000 AND itemid < 221000 )) INHERITS (chartevents);
CREATE TABLE chartevents_2 ( CHECK ( itemid >= 221000 AND itemid < 222000 )) INHERITS (chartevents);
CREATE TABLE chartevents_3 ( CHECK ( itemid >= 222000 AND itemid < 223000 )) INHERITS (chartevents);
CREATE TABLE chartevents_4 ( CHECK ( itemid >= 223000 AND itemid < 224000 )) INHERITS (chartevents);
CREATE TABLE chartevents_5 ( CHECK ( itemid >= 224000 AND itemid < 225000 )) INHERITS (chartevents);
CREATE TABLE chartevents_6 ( CHECK ( itemid >= 225000 AND itemid < 226000 )) INHERITS (chartevents);
CREATE TABLE chartevents_7 ( CHECK ( itemid >= 226000 AND itemid < 227000 )) INHERITS (chartevents);
CREATE TABLE chartevents_8 ( CHECK ( itemid >= 227000 AND itemid < 228000 )) INHERITS (chartevents);
CREATE TABLE chartevents_9 ( CHECK ( itemid >= 228000 AND itemid < 229000 )) INHERITS (chartevents);
CREATE TABLE chartevents_10 ( CHECK ( itemid >= 229000 AND itemid < 230000 )) INHERITS (chartevents);


CREATE OR REPLACE FUNCTION chartevents_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF ( NEW.itemid >= 220000 AND NEW.itemid < 221000 ) THEN INSERT INTO chartevents_1 VALUES (NEW.*);
ELSIF ( NEW.itemid >= 221000 AND NEW.itemid < 222000 ) THEN INSERT INTO chartevents_2 VALUES (NEW.*);
ELSIF ( NEW.itemid >= 222000 AND NEW.itemid < 223000 ) THEN INSERT INTO chartevents_3 VALUES (NEW.*);
ELSIF ( NEW.itemid >= 223000 AND NEW.itemid < 224000 ) THEN INSERT INTO chartevents_4 VALUES (NEW.*);
ELSIF ( NEW.itemid >= 224000 AND NEW.itemid < 225000 ) THEN INSERT INTO chartevents_5 VALUES (NEW.*);
ELSIF ( NEW.itemid >= 225000 AND NEW.itemid < 226000 ) THEN INSERT INTO chartevents_6 VALUES (NEW.*);
ELSIF ( NEW.itemid >= 226000 AND NEW.itemid < 227000 ) THEN INSERT INTO chartevents_7 VALUES (NEW.*);
ELSIF ( NEW.itemid >= 227000 AND NEW.itemid < 228000 ) THEN INSERT INTO chartevents_8 VALUES (NEW.*);
ELSIF ( NEW.itemid >= 228000 AND NEW.itemid < 229000 ) THEN INSERT INTO chartevents_9 VALUES (NEW.*);
ELSIF ( NEW.itemid >= 229000 AND NEW.itemid < 230000 ) THEN INSERT INTO chartevents_10 VALUES (NEW.*);
ELSE
	INSERT INTO chartevents_null VALUES (NEW.*);
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER insert_chartevents_trigger
    BEFORE INSERT ON chartevents
    FOR EACH ROW EXECUTE PROCEDURE chartevents_insert_trigger();