search

OHDSI / CommonDataModel

Definition and DDLs for the OMOP Common Data Model (CDM)
https://ohdsi.github.io/CommonDataModel
877 stars 448 forks source link

datetime2 does not exist in postgres #294

Closed jcabrerazuniga closed 3 years ago

jcabrerazuniga commented 5 years ago

I tried uploading the sql info at:

https://github.com/OHDSI/CommonDataModel/tree/v5.3.1/PostgreSQL

But I got the next errors while running the at the ddl file:

ERROR: type "datetime2" does not exist LINE 9: metadata_datetime DATETIME2 NULL ^ ERROR: type "datetime2" does not exist LINE 14: birth_datetime DATETIME2 NULL, ^ CREATE TABLE ERROR: type "datetime2" does not exist LINE 8: specimen_datetime DATETIME2 NULL , ^ ERROR: type "datetime2" does not exist LINE 5: death_datetime DATETIME2 NULL , ^ ERROR: type "datetime2" does not exist LINE 7: visit_start_datetime DATETIME2 NULL , ^ ERROR: type "datetime2" does not exist LINE 7: visit_detail_start_datetime DATETIME2 NULL , ^ ERROR: type "datetime2" does not exist LINE 7: procedure_datetime DATETIME2 NULL , ^ ERROR: type "datetime2" does not exist LINE 7: drug_exposure_start_datetime DATETIME2 NULL , ^ ERROR: type "datetime2" does not exist LINE 7: device_exposure_start_datetime DATETIME2 NULL , ^ ERROR: type "datetime2" does not exist LINE 7: condition_start_datetime DATETIME2 NULL , ^ ERROR: type "datetime2" does not exist LINE 7: measurement_datetime DATETIME2 NULL , ^ ERROR: type "datetime2" does not exist LINE 6: note_datetime DATETIME2 NULL , ^ ERROR: type "datetime2" does not exist LINE 13: nlp_datetime DATETIME2 NULL , ^ ERROR: type "datetime2" does not exist LINE 7: observation_datetime DATETIME2 NULL , ^ Seems that datetime2 is not supported in Postgress. Any ideas about how to solve this issue?

evgenii-vilchinsky commented 5 years ago

Same error while trying to run OMOP CDM postgresql ddl.txt CDM v5.3.1 updates

anthonysena commented 5 years ago

I also got the same error using this PostgreSQL DDL Script on PostgreSQL 9.6.9. I was able to work-around this issue by using SqlRender to translate this script to the PostgreSQL dialect. You can do this via: http://data.ohdsi.org/SqlDeveloper/.

Using SqlRender is not mentioned in the read me so it may be that this script needs an update. Tagging @clairblacketer for awareness and guidance on this item.

clairblacketer commented 5 years ago

Thanks all - the postgres script for CDM v5.3.1 was created using SqlRender but was done before this datetime fix was added to the package. We are exploring the best ways to incorporate historical fixes into the CDM versions so thank you for bringing this up.

shabiel commented 4 years ago

This affects me too. I am happy to fix it and make a pull request based on @mgurley's fix.

Should I do that?

clairblacketer commented 3 years ago

Hi thank you for the offer @shabiel but the DDLs are actually developed in a separate repository and this issue has been fixed there using SqlRender. The newly formed CDM development group will work to reconcile these historical bugs this year.

clairblacketer commented 3 years ago

Moving this to done as it addressed by SqlRender