create new omop dataset database

[huyuaaaray]

1. start from https://github.com/OHDSI/CommonDataModel/tree/main/ddl/5.4/postgresql ddl (no need to edit sql files)
2. Then load in the clinical files (https://ohdsi.github.io/CommonDataModel/cdm53.html#measurement edit the different common data model version if needed)
3. Load the vocabulary files subsequently.
4. load the other ddl files:
   • primary key
   • constraints
   • indices
   • vocabulary
5. Load 5 f_ suffix ddl files.
6. Use fhir_names ETL to load f_person if your database already has data in it. Read the instruction in fhir_names/ folder.

   
   psql -h ahc-hobi-gpu01.ahc.ufl.edu -p 5432 -U postgres -W -d new_adrd1 -f /omop_ddl/names.dmp

psql -h ahc-hobi-gpu01.ahc.ufl.edu -p 5432 -U postgres -W -d new_adrd1 -f /omop_ddl/insert_names_ti_f_person.sql

[huyuaaaray]

For the clinical files loading: I have tried to process all the csvs as below:

COPY person(
    person_id, 
    gender_concept_id, 
    year_of_birth, 
    month_of_birth, 
    day_of_birth, 
    birth_datetime, 
    race_concept_id, 
    ethnicity_concept_id, 
    location_id, 
    provider_id, 
    care_site_id, 
    person_source_value, 
    gender_source_value, 
    gender_source_concept_id, 
    race_source_value, 
    race_source_concept_id, 
    ethnicity_source_value, 
    ethnicity_source_concept_id
) 
FROM '/csv_data/person.csv' 
WITH DELIMITER '|' CSV HEADER NULL 'NULL';

COPY CONDITION_OCCURRENCE(
    condition_occurrence_id, 
    person_id, 
    condition_concept_id, 
    condition_start_date, 
    condition_start_datetime, 
    condition_end_date, 
    condition_end_datetime, 
    condition_type_concept_id, 
    condition_status_concept_id, 
    stop_reason, 
    provider_id, 
    visit_occurrence_id, 
    visit_detail_id, 
    condition_source_value, 
    condition_source_concept_id, 
    condition_status_source_value
) 
FROM '/csv_data/condition_occurrence.csv' 
WITH DELIMITER '|' CSV HEADER NULL 'NULL';

COPY DEATH(person_id,death_date,death_datetime,death_type_concept_id,cause_concept_id,cause_source_value,cause_source_concept_id) FROM '/csv_data/death.csv' WITH DELIMITER '|' CSV HEADER;

COPY DRUG_EXPOSURE(drug_exposure_id,person_id,drug_concept_id,drug_exposure_start_date,drug_exposure_start_datetime,drug_exposure_end_date,drug_exposure_end_datetime,verbatim_end_date,drug_type_concept_id,stop_reason,refills,quantity,days_supply,sig,route_concept_id,lot_number,provider_id,visit_occurrence_id,visit_detail_id,drug_source_value,drug_source_concept_id,route_source_value,dose_unit_source_value) FROM '/csv_data/drug_exposure.csv' WITH DELIMITER '|' CSV HEADER;

COPY DEVICE_EXPOSURE(device_exposure_id,person_id,device_concept_id,device_exposure_start_date,device_exposure_start_datetime,device_exposure_end_date,device_exposure_end_datetime,device_type_concept_id,unique_device_id,production_id,quantity,provider_id,visit_occurrence_id,visit_detail_id,device_source_value,device_source_concept_id,unit_concept_id,unit_source_value,unit_source_concept_id) FROM '/csv_data/device_exposure.csv' WITH DELIMITER '|' CSV HEADER;

COPY LOCATION(location_id,address_1,address_2,city,state,zip,county,location_source_value,country_concept_id,country_source_value,latitude,longitude) FROM '/csv_data/location.csv' WITH DELIMITER '|' CSV HEADER;

COPY MEASUREMENT(measurement_id,person_id,measurement_concept_id,measurement_date,measurement_datetime,measurement_time,measurement_type_concept_id,operator_concept_id,value_as_number,value_as_concept_id,unit_concept_id,range_low,range_high,provider_id,visit_occurrence_id,visit_detail_id,measurement_source_value,measurement_source_concept_id,unit_source_value,unit_source_concept_id,value_source_value,measurement_event_id,meas_event_field_concept_id,modifier_of_event_id,modifier_of_field_concept_id) FROM '/csv_data/measurement.csv' WITH DELIMITER '|' CSV HEADER;

COPY OBSERVATION(observation_id,person_id,observation_concept_id,observation_date,observation_datetime,observation_type_concept_id,value_as_number,value_as_string,value_as_concept_id,qualifier_concept_id,unit_concept_id,provider_id,visit_occurrence_id,visit_detail_id,observation_source_value,observation_source_concept_id,unit_source_value,qualifier_source_value,value_source_value,observation_event_id,obs_event_field_concept_id) FROM '/csv_data/observation.csv' WITH DELIMITER '|' CSV HEADER;

COPY OBSERVATION_PERIOD(observation_period_id,person_id,observation_period_start_date,observation_period_end_date,period_type_concept_id) FROM '/csv_data/observation_period.csv' WITH DELIMITER '|' CSV HEADER;

COPY PROCEDURE_OCCURRENCE(procedure_occurrence_id,person_id,procedure_concept_id,procedure_date,procedure_datetime,procedure_end_date,procedure_end_datetime,procedure_type_concept_id,modifier_concept_id,quantity,provider_id,visit_occurrence_id,visit_detail_id,procedure_source_value,procedure_source_concept_id,modifier_source_value) FROM '/csv_data/procedure_occurrence.csv' WITH DELIMITER '|' CSV HEADER;

COPY VISIT_OCCURRENCE(visit_occurrence_id,person_id,visit_concept_id,visit_start_date,visit_start_datetime,visit_end_date,visit_end_datetime,visit_type_concept_id,provider_id,care_site_id,visit_source_value,visit_source_concept_id,admitting_source_concept_id,admitting_source_value,discharge_to_concept_id,discharge_to_source_value,preceding_visit_occurrence_id) FROM '/csv_data/visit_occurrence.csv' WITH DELIMITER '|' CSV HEADER;

But only the person, CONDITION_OCCURRENCE, death, MEASUREMENT, OBSERVATION, OBSERVATION_PERIOD, PROCEDURE_OCCURRENCE，VISIT_OCCURRENCE， location table successfully load in.

issue1:

ERROR:  value "6991999997739063" is out of range for type integer
CONTEXT:  COPY person, line 2, column person_id: "6991999997739063" 

solution:

ALTER TABLE person 
ALTER COLUMN person_id TYPE bigint;

Then successfully load in: COPY 42704

Then change other table's type:

ALTER TABLE condition_occurrence
    ALTER COLUMN condition_occurrence_id TYPE bigint;

ALTER TABLE condition_occurrence
    ALTER COLUMN person_id TYPE bigint;

ALTER TABLE condition_occurrence
    ALTER COLUMN condition_concept_id TYPE bigint;

ALTER TABLE condition_occurrence
    ALTER COLUMN condition_type_concept_id TYPE bigint;

ALTER TABLE condition_occurrence
    ALTER COLUMN condition_status_concept_id TYPE bigint;

ALTER TABLE condition_occurrence
    ALTER COLUMN provider_id TYPE bigint;

ALTER TABLE condition_occurrence
    ALTER COLUMN visit_occurrence_id TYPE bigint;

ALTER TABLE condition_occurrence
    ALTER COLUMN visit_detail_id TYPE bigint;

ALTER TABLE condition_occurrence
    ALTER COLUMN condition_source_concept_id TYPE bigint;

ALTER TABLE death
    ALTER COLUMN person_id TYPE bigint;

ALTER TABLE death
    ALTER COLUMN death_type_concept_id TYPE bigint;

ALTER TABLE death
    ALTER COLUMN cause_concept_id TYPE bigint;

ALTER TABLE death
    ALTER COLUMN cause_source_concept_id TYPE bigint;
ALTER TABLE drug_exposure
    ALTER COLUMN drug_exposure_id TYPE bigint;

ALTER TABLE drug_exposure
    ALTER COLUMN person_id TYPE bigint;

ALTER TABLE drug_exposure
    ALTER COLUMN drug_concept_id TYPE bigint;

ALTER TABLE drug_exposure
    ALTER COLUMN drug_type_concept_id TYPE bigint;

ALTER TABLE drug_exposure
    ALTER COLUMN route_concept_id TYPE bigint;

ALTER TABLE drug_exposure
    ALTER COLUMN provider_id TYPE bigint;

ALTER TABLE drug_exposure
    ALTER COLUMN visit_occurrence_id TYPE bigint;

ALTER TABLE drug_exposure
    ALTER COLUMN visit_detail_id TYPE bigint;

ALTER TABLE drug_exposure
    ALTER COLUMN drug_source_concept_id TYPE bigint;
ALTER TABLE device_exposure
    ALTER COLUMN device_exposure_id TYPE bigint;

ALTER TABLE device_exposure
    ALTER COLUMN person_id TYPE bigint;

ALTER TABLE device_exposure
    ALTER COLUMN device_concept_id TYPE bigint;

ALTER TABLE device_exposure
    ALTER COLUMN device_type_concept_id TYPE bigint;

ALTER TABLE device_exposure
    ALTER COLUMN provider_id TYPE bigint;

ALTER TABLE device_exposure
    ALTER COLUMN visit_occurrence_id TYPE bigint;

ALTER TABLE device_exposure
    ALTER COLUMN visit_detail_id TYPE bigint;

ALTER TABLE device_exposure
    ALTER COLUMN device_source_concept_id TYPE bigint;

ALTER TABLE device_exposure
    ALTER COLUMN unit_concept_id TYPE bigint;

ALTER TABLE device_exposure
    ALTER COLUMN unit_source_concept_id TYPE bigint;
ALTER TABLE location
    ALTER COLUMN location_id TYPE bigint;

ALTER TABLE location
    ALTER COLUMN country_concept_id TYPE bigint;
ALTER TABLE measurement
    ALTER COLUMN measurement_id TYPE bigint;

ALTER TABLE measurement
    ALTER COLUMN person_id TYPE bigint;

ALTER TABLE measurement
    ALTER COLUMN measurement_concept_id TYPE bigint;

ALTER TABLE measurement
    ALTER COLUMN measurement_type_concept_id TYPE bigint;

ALTER TABLE measurement
    ALTER COLUMN operator_concept_id TYPE bigint;

ALTER TABLE measurement
    ALTER COLUMN value_as_concept_id TYPE bigint;

ALTER TABLE measurement
    ALTER COLUMN unit_concept_id TYPE bigint;

ALTER TABLE measurement
    ALTER COLUMN provider_id TYPE bigint;

ALTER TABLE measurement
    ALTER COLUMN visit_occurrence_id TYPE bigint;

ALTER TABLE measurement
    ALTER COLUMN visit_detail_id TYPE bigint;

ALTER TABLE measurement
    ALTER COLUMN measurement_source_concept_id TYPE bigint;

ALTER TABLE measurement
    ALTER COLUMN unit_source_concept_id TYPE bigint;

ALTER TABLE measurement
    ALTER COLUMN measurement_event_id TYPE bigint;

ALTER TABLE measurement
    ALTER COLUMN meas_event_field_concept_id TYPE bigint;

ALTER TABLE measurement
    ALTER COLUMN modifier_of_event_id TYPE bigint;

ALTER TABLE measurement
    ALTER COLUMN modifier_of_field_concept_id TYPE bigint;
ALTER TABLE observation
    ALTER COLUMN observation_id TYPE bigint;

ALTER TABLE observation
    ALTER COLUMN person_id TYPE bigint;

ALTER TABLE observation
    ALTER COLUMN observation_concept_id TYPE bigint;

ALTER TABLE observation
    ALTER COLUMN observation_type_concept_id TYPE bigint;

ALTER TABLE observation
    ALTER COLUMN value_as_concept_id TYPE bigint;

ALTER TABLE observation
    ALTER COLUMN qualifier_concept_id TYPE bigint;

ALTER TABLE observation
    ALTER COLUMN unit_concept_id TYPE bigint;

ALTER TABLE observation
    ALTER COLUMN provider_id TYPE bigint;

ALTER TABLE observation
    ALTER COLUMN visit_occurrence_id TYPE bigint;

ALTER TABLE observation
    ALTER COLUMN visit_detail_id TYPE bigint;

ALTER TABLE observation
    ALTER COLUMN observation_source_concept_id TYPE bigint;

ALTER TABLE observation
    ALTER COLUMN unit_source_concept_id TYPE bigint;

ALTER TABLE observation
    ALTER COLUMN observation_event_id TYPE bigint;

ALTER TABLE observation
    ALTER COLUMN obs_event_field_concept_id TYPE bigint;
ALTER TABLE observation_period
    ALTER COLUMN observation_period_id TYPE bigint;

ALTER TABLE observation_period
    ALTER COLUMN person_id TYPE bigint;

ALTER TABLE observation_period
    ALTER COLUMN period_type_concept_id TYPE bigint;
ALTER TABLE procedure_occurrence
    ALTER COLUMN procedure_occurrence_id TYPE bigint;

ALTER TABLE procedure_occurrence
    ALTER COLUMN person_id TYPE bigint;

ALTER TABLE procedure_occurrence
    ALTER COLUMN procedure_concept_id TYPE bigint;

ALTER TABLE procedure_occurrence
    ALTER COLUMN procedure_type_concept_id TYPE bigint;

ALTER TABLE procedure_occurrence
    ALTER COLUMN modifier_concept_id TYPE bigint;

ALTER TABLE procedure_occurrence
    ALTER COLUMN provider_id TYPE bigint;

ALTER TABLE procedure_occurrence
    ALTER COLUMN visit_occurrence_id TYPE bigint;

ALTER TABLE procedure_occurrence
    ALTER COLUMN visit_detail_id TYPE bigint;

ALTER TABLE procedure_occurrence
    ALTER COLUMN procedure_source_concept_id TYPE bigint;
ALTER TABLE visit_occurrence
    ALTER COLUMN visit_occurrence_id TYPE bigint;

ALTER TABLE visit_occurrence
    ALTER COLUMN person_id TYPE bigint;

ALTER TABLE visit_occurrence
    ALTER COLUMN visit_concept_id TYPE bigint;

ALTER TABLE visit_occurrence
    ALTER COLUMN visit_type_concept_id TYPE bigint;

ALTER TABLE visit_occurrence
    ALTER COLUMN provider_id TYPE bigint;

ALTER TABLE visit_occurrence
    ALTER COLUMN care_site_id TYPE bigint;

ALTER TABLE visit_occurrence
    ALTER COLUMN visit_source_concept_id TYPE bigint;

ALTER TABLE visit_occurrence
    ALTER COLUMN admitting_source_concept_id TYPE bigint;

ALTER TABLE visit_occurrence
    ALTER COLUMN discharge_to_concept_id TYPE bigint;

ALTER TABLE visit_occurrence
    ALTER COLUMN preceding_visit_occurrence_id TYPE bigint;
ALTER TABLE visit_occurrence
    ALTER COLUMN admitting_source_value TYPE text;
ALTER TABLE visit_occurrence
    ALTER COLUMN discharge_to_source_value TYPE text;

issue 2 drug_exposure:

ERROR:  invalid byte sequence for encoding "UTF8": 0xff
CONTEXT:  COPY drug_exposure, line 3559797

issue 3 measurement: ERROR: invalid byte sequence for encoding "UTF8": 0xe6 0x49 0x55 CONTEXT: COPY measurement, line 687999

issue 4 device_exposure:

new_adrd1=# COPY DEVICE_EXPOSURE(device_exposure_id,person_id,device_concept_id,device_exposure_start_date,device_exposure_start_datetime,device_exposure_end_date,device_exposure_end_datetime,device_type_concept_id,unique_device_id,production_id,quantity,provider_id,visit_occurrence_id,visit_detail_id,device_source_value,device_source_concept_id,unit_concept_id,unit_source_value,unit_source_concept_id) FROM '/csv_data/device_exposure.csv' WITH DELIMITER '|' CSV HEADER NULL 'NULL';
COPY 1

[huyuaaaray]

For the vocabulary files: I have successfully load in 9 files as follow:

\copy DRUG_STRENGTH FROM '/hexing/DRUG_STRENGTH.csv' WITH DELIMITER E'\t' CSV HEADER QUOTE E'\b' ;

\copy CONCEPT FROM '/hexing/CONCEPT.csv' WITH DELIMITER E'\t' CSV HEADER QUOTE E'\b' ;

\copy CONCEPT_RELATIONSHIP FROM '/hexing/CONCEPT_RELATIONSHIP.csv' WITH DELIMITER E'\t' CSV HEADER QUOTE E'\b' ;

\copy CONCEPT_ANCESTOR FROM '/hexing/CONCEPT_ANCESTOR.csv' WITH DELIMITER E'\t' CSV HEADER QUOTE E'\b' ;

\copy CONCEPT_SYNONYM FROM '/hexing/CONCEPT_SYNONYM.csv' WITH DELIMITER E'\t' CSV HEADER QUOTE E'\b' ;

\copy VOCABULARY FROM '/hexing/VOCABULARY.csv' WITH DELIMITER E'\t' CSV HEADER QUOTE E'\b' ;

\copy RELATIONSHIP FROM '/hexing/RELATIONSHIP.csv' WITH DELIMITER E'\t' CSV HEADER QUOTE E'\b' ;

\copy CONCEPT_CLASS FROM '/hexing/CONCEPT_CLASS.csv' WITH DELIMITER E'\t' CSV HEADER QUOTE E'\b' ;

\copy DOMAIN FROM '/hexing/DOMAIN.csv' WITH DELIMITER E'\t' CSV HEADER QUOTE E'\b' ;

Results:

COPY 2981807
COPY 7407686
COPY 46545874
COPY 80436514
COPY 2741949
COPY 91
COPY 696
COPY 423
COPY 50

[huyuaaaray]

For the 4 ddl files, the primary_keys and constraints release errors as follow: primary_keys:

psql:/omop_ddl/OMOPCDM_postgresql_5.4_primary_keys.sql:3: ERROR:  multiple primary keys for table "person" are not allowed
psql:/omop_ddl/OMOPCDM_postgresql_5.4_primary_keys.sql:5: ERROR:  multiple primary keys for table "observation_period" are not allowed
psql:/omop_ddl/OMOPCDM_postgresql_5.4_primary_keys.sql:7: ERROR:  multiple primary keys for table "visit_occurrence" are not allowed
psql:/omop_ddl/OMOPCDM_postgresql_5.4_primary_keys.sql:9: ERROR:  multiple primary keys for table "visit_detail" are not allowed
psql:/omop_ddl/OMOPCDM_postgresql_5.4_primary_keys.sql:11: ERROR:  multiple primary keys for table "condition_occurrence" are not allowed
psql:/omop_ddl/OMOPCDM_postgresql_5.4_primary_keys.sql:13: ERROR:  multiple primary keys for table "drug_exposure" are not allowed
psql:/omop_ddl/OMOPCDM_postgresql_5.4_primary_keys.sql:15: ERROR:  multiple primary keys for table "procedure_occurrence" are not allowed
psql:/omop_ddl/OMOPCDM_postgresql_5.4_primary_keys.sql:17: ERROR:  multiple primary keys for table "device_exposure" are not allowed
psql:/omop_ddl/OMOPCDM_postgresql_5.4_primary_keys.sql:19: ERROR:  multiple primary keys for table "measurement" are not allowed
psql:/omop_ddl/OMOPCDM_postgresql_5.4_primary_keys.sql:21: ERROR:  multiple primary keys for table "observation" are not allowed
psql:/omop_ddl/OMOPCDM_postgresql_5.4_primary_keys.sql:23: ERROR:  multiple primary keys for table "note" are not allowed
psql:/omop_ddl/OMOPCDM_postgresql_5.4_primary_keys.sql:25: ERROR:  multiple primary keys for table "note_nlp" are not allowed
psql:/omop_ddl/OMOPCDM_postgresql_5.4_primary_keys.sql:27: ERROR:  multiple primary keys for table "specimen" are not allowed
psql:/omop_ddl/OMOPCDM_postgresql_5.4_primary_keys.sql:29: ERROR:  multiple primary keys for table "location" are not allowed
psql:/omop_ddl/OMOPCDM_postgresql_5.4_primary_keys.sql:31: ERROR:  multiple primary keys for table "care_site" are not allowed
psql:/omop_ddl/OMOPCDM_postgresql_5.4_primary_keys.sql:33: ERROR:  multiple primary keys for table "provider" are not allowed
psql:/omop_ddl/OMOPCDM_postgresql_5.4_primary_keys.sql:35: ERROR:  multiple primary keys for table "payer_plan_period" are not allowed
psql:/omop_ddl/OMOPCDM_postgresql_5.4_primary_keys.sql:37: ERROR:  multiple primary keys for table "cost" are not allowed
psql:/omop_ddl/OMOPCDM_postgresql_5.4_primary_keys.sql:39: ERROR:  multiple primary keys for table "drug_era" are not allowed
psql:/omop_ddl/OMOPCDM_postgresql_5.4_primary_keys.sql:41: ERROR:  multiple primary keys for table "dose_era" are not allowed
psql:/omop_ddl/OMOPCDM_postgresql_5.4_primary_keys.sql:43: ERROR:  multiple primary keys for table "condition_era" are not allowed
psql:/omop_ddl/OMOPCDM_postgresql_5.4_primary_keys.sql:45: ERROR:  multiple primary keys for table "episode" are not allowed
psql:/omop_ddl/OMOPCDM_postgresql_5.4_primary_keys.sql:47: ERROR:  multiple primary keys for table "metadata" are not allowed
psql:/omop_ddl/OMOPCDM_postgresql_5.4_primary_keys.sql:49: ERROR:  multiple primary keys for table "concept" are not allowed
psql:/omop_ddl/OMOPCDM_postgresql_5.4_primary_keys.sql:51: ERROR:  multiple primary keys for table "vocabulary" are not allowed
psql:/omop_ddl/OMOPCDM_postgresql_5.4_primary_keys.sql:53: ERROR:  multiple primary keys for table "domain" are not allowed
psql:/omop_ddl/OMOPCDM_postgresql_5.4_primary_keys.sql:55: ERROR:  multiple primary keys for table "concept_class" are not allowed
psql:/omop_ddl/OMOPCDM_postgresql_5.4_primary_keys.sql:57: ERROR:  multiple primary keys for table "relationship" are not allowed

constraints:

psql:/omop_ddl/OMOPCDM_postgresql_5.4_constraints.sql:11: ERROR:  insert or update on table "person" violates foreign key constraint "fpk_person_provider_id"
DETAIL:  Key (provider_id)=(6992000000095924) is not present in table "provider".
psql:/omop_ddl/OMOPCDM_postgresql_5.4_constraints.sql:13: ERROR:  insert or update on table "person" violates foreign key constraint "fpk_person_care_site_id"
DETAIL:  Key (care_site_id)=(2731) is not present in table "care_site".

psql:/omop_ddl/OMOPCDM_postgresql_5.4_constraints.sql:21: ERROR:  insert or update on table "observation_period" violates foreign key constraint "fpk_observation_period_person_id"
DETAIL:  Key (person_id)=(6992000002310653) is not present in table "person".

psql:/omop_ddl/OMOPCDM_postgresql_5.4_constraints.sql:25: ERROR:  insert or update on table "visit_occurrence" violates foreign key constraint "fpk_visit_occurrence_person_id"
DETAIL:  Key (person_id)=(6991999999939186) is not present in table "person".

psql:/omop_ddl/OMOPCDM_postgresql_5.4_constraints.sql:31: ERROR:  insert or update on table "visit_occurrence" violates foreign key constraint "fpk_visit_occurrence_provider_id"
DETAIL:  Key (provider_id)=(6991999999868939) is not present in table "provider".
psql:/omop_ddl/OMOPCDM_postgresql_5.4_constraints.sql:33: ERROR:  insert or update on table "visit_occurrence" violates foreign key constraint "fpk_visit_occurrence_care_site_id"
DETAIL:  Key (care_site_id)=(2879) is not present in table "care_site".

psql:/omop_ddl/OMOPCDM_postgresql_5.4_constraints.sql:65: ERROR:  insert or update on table "condition_occurrence" violates foreign key constraint "fpk_condition_occurrence_person_id"
DETAIL:  Key (person_id)=(6992000004051339) is not present in table "person".

psql:/omop_ddl/OMOPCDM_postgresql_5.4_constraints.sql:73: ERROR:  insert or update on table "condition_occurrence" violates foreign key constraint "fpk_condition_occurrence_provider_id"
DETAIL:  Key (provider_id)=(6992000000097937) is not present in table "provider".
psql:/omop_ddl/OMOPCDM_postgresql_5.4_constraints.sql:75: ERROR:  insert or update on table "condition_occurrence" violates foreign key constraint "fpk_condition_occurrence_visit_occurrence_id"
DETAIL:  Key (visit_occurrence_id)=(6991999897003105) is not present in table "visit_occurrence".
psql:/omop_ddl/OMOPCDM_postgresql_5.4_constraints.sql:77: ERROR:  insert or update on table "condition_occurrence" violates foreign key constraint "fpk_condition_occurrence_visit_detail_id"
DETAIL:  Key (visit_detail_id)=(6992000005767771) is not present in table "visit_detail".

psql:/omop_ddl/OMOPCDM_postgresql_5.4_constraints.sql:97: ERROR:  insert or update on table "procedure_occurrence" violates foreign key constraint "fpk_procedure_occurrence_person_id"
DETAIL:  Key (person_id)=(6992000000122060) is not present in table "person".
psql:/omop_ddl/OMOPCDM_postgresql_5.4_constraints.sql:99: ERROR:  insert or update on table "procedure_occurrence" violates foreign key constraint "fpk_procedure_occurrence_procedure_concept_id"
DETAIL:  Key (procedure_concept_id)=(999000009) is not present in table "concept".

psql:/omop_ddl/OMOPCDM_postgresql_5.4_constraints.sql:105: ERROR:  insert or update on table "procedure_occurrence" violates foreign key constraint "fpk_procedure_occurrence_provider_id"
DETAIL:  Key (provider_id)=(6992000000004690) is not present in table "provider".
psql:/omop_ddl/OMOPCDM_postgresql_5.4_constraints.sql:107: ERROR:  insert or update on table "procedure_occurrence" violates foreign key constraint "fpk_procedure_occurrence_visit_occurrence_id"
DETAIL:  Key (visit_occurrence_id)=(6991999924201580) is not present in table "visit_occurrence".
psql:/omop_ddl/OMOPCDM_postgresql_5.4_constraints.sql:109: ERROR:  insert or update on table "procedure_occurrence" violates foreign key constraint "fpk_procedure_occurrence_visit_detail_id"
DETAIL:  Key (visit_detail_id)=(6991999973862828) is not present in table "visit_detail".
psql:/omop_ddl/OMOPCDM_postgresql_5.4_constraints.sql:111: ERROR:  insert or update on table "procedure_occurrence" violates foreign key constraint "fpk_procedure_occurrence_procedure_source_concept_id"
DETAIL:  Key (procedure_source_concept_id)=(999000009) is not present in table "concept".

psql:/omop_ddl/OMOPCDM_postgresql_5.4_constraints.sql:119: ERROR:  insert or update on table "device_exposure" violates foreign key constraint "fpk_device_exposure_provider_id"
DETAIL:  Key (provider_id)=(6992000000339719) is not present in table "provider".

psql:/omop_ddl/OMOPCDM_postgresql_5.4_constraints.sql:123: ERROR:  insert or update on table "device_exposure" violates foreign key constraint "fpk_device_exposure_visit_detail_id"
DETAIL:  Key (visit_detail_id)=(6992000007939320) is not present in table "visit_detail".

psql:/omop_ddl/OMOPCDM_postgresql_5.4_constraints.sql:155: ERROR:  insert or update on table "observation" violates foreign key constraint "fpk_observation_person_id"
DETAIL:  Key (person_id)=(6992000001253131) is not present in table "person".

psql:/omop_ddl/OMOPCDM_postgresql_5.4_constraints.sql:321: ERROR:  insert or update on table "concept_relationship" violates foreign key constraint "fpk_concept_relationship_concept_id_1"
DETAIL:  Key (concept_id_1)=(42627868) is not present in table "concept".
psql:/omop_ddl/OMOPCDM_postgresql_5.4_constraints.sql:323: ERROR:  insert or update on table "concept_relationship" violates foreign key constraint "fpk_concept_relationship_concept_id_2"
DETAIL:  Key (concept_id_2)=(42627868) is not present in table "concept".

psql:/omop_ddl/OMOPCDM_postgresql_5.4_constraints.sql:329: ERROR:  insert or update on table "concept_synonym" violates foreign key constraint "fpk_concept_synonym_concept_id"
DETAIL:  Key (concept_id)=(42628370) is not present in table "concept".

psql:/omop_ddl/OMOPCDM_postgresql_5.4_constraints.sql:333: ERROR:  insert or update on table "concept_ancestor" violates foreign key constraint "fpk_concept_ancestor_ancestor_concept_id"
DETAIL:  Key (ancestor_concept_id)=(709789) is not present in table "concept".
psql:/omop_ddl/OMOPCDM_postgresql_5.4_constraints.sql:335: ERROR:  insert or update on table "concept_ancestor" violates foreign key constraint "fpk_concept_ancestor_descendant_concept_id"
DETAIL:  Key (descendant_concept_id)=(725046) is not present in table "concept".
psql:/omop_ddl/OMOPCDM_postgresql_5.4_constraints.sql:353: ERROR:  there is no unique constraint matching given keys for referenced table "cohort"

[huyuaaaray]

load f_person file issue:

psql:/omop_ddl/insert_names_to_f_person.sql:26: ERROR:  integer out of range
psql:/omop_ddl/insert_names_to_f_person.sql:55: ERROR:  integer out of range

solution: ALTER TABLE f_person ALTER COLUMN person_id TYPE bigint;