Postgres constraint error

[DJBoeke]

I'm having troubles running the constraint.sql file. The primary keys till labevents are made and the correct tables are altered but from that table on, it gives errors like this:

.../constraint.sql:79: ERROR: could not create unique index "labevents_pk"
DETAIL: Key (labevent_id)=(1) is duplicated.

.../constraint.sql:86: ERROR: could not create unique index "microbiologyevents_pk"
DETAIL: Key (microevent_id)=(1) is duplicated.

.../constraint.sql:93: ERROR: could not create unique index "patients_pk"
DETAIL: Key (subject_id)=(15012261) is duplicated.

[alistairewj]

This sounds like you have inserted the data twice into those tables. I would try to re-load the data into those tables, something like:

TRUNCATE TABLE mimiciv_hosp.patients;
\COPY mimiciv_hosp.patients FROM PROGRAM 'gzip -dc patients.csv.gz' DELIMITER ',' CSV HEADER NULL '';

TRUNCATE TABLE mimiciv_hosp.microbiologyevents;
\COPY mimiciv_hosp.microbiologyevents FROM PROGRAM 'gzip -dc microbiologyevents.csv.gz' DELIMITER ',' CSV HEADER NULL '';

TRUNCATE TABLE mimiciv_hosp.labevents;
\COPY mimiciv_hosp.labevents FROM PROGRAM 'gzip -dc labevents.csv.gz' DELIMITER ',' CSV HEADER NULL '';

You might need to change the \COPY command if you loaded directly from CSV. Try it with the patients table first since it's so small. If that works then you can carry on with labevents and microbiologyevents which are much bigger. At the end, you can verify you have the right row counts with the validate SQL script.