Postgresql code is not working

[Enrico68]

Hello,

Interested in your SQL implementation of SnomedCT I wanted to give it a try with PostgreSQL. This is what i have done (I am a beginner in SQL , so sorry for mistakes or whatever..)

~/download/SNOMED-CT-Database-master%=> creatdb snomedct 
~/download/SNOMED-CT-Database-master%=> psql -d snomedct -f create-database-postgres.sql

So I have modified the load-postgresql.sql according to the location of SnomedCT and changing the delimiter

/* loads the SNOMED CT 'Full' release - replace filenames with relevant locations of base SNOMED CT release files*/
/* Filenames may need to change depending on the release you wish to upload, currently set to January 2015 release */

use snomedct;

COPY curr_concept_f(id, effectivetime, active, moduleid, definitionstatusid) 
FROM '/Users/enricopirani/download/RF2Release/Full/Terminology/sct2_Concept_Full_INT_20150131.txt' 
WITH DELIMITER '~';
CSV HEADER;


COPY curr_description_f(id, effectivetime, active, moduleid, conceptid, languagecode, typeid, term, casesignificanceid)
FROM '/Users/enricopirani/download/RF2Release/Full/Terminology/sct2_Description_Full-en_INT_20150131.txt' 
WITH DELIMITER '~';
CSV HEADER;


COPY curr_textdefinition_f(id, effectivetime, active, moduleid, conceptid, languagecode, typeid, term, casesignificanceid)
FROM '/Users/enricopirani/download/RF2Release/Full/Terminology/sct2_TextDefinition_Full-en_INT_20150131.txt'
WITH DELIMITER '~';
CSV HEADER;


COPY curr_relationship_f(id, effectivetime, active, moduleid, sourceid, destinationid, relationshipgroup, typeid,characteristictypeid, modifierid)
FROM '/Users/enricopirani/download/RF2Release/Full/Terminology/sct2_Relationship_Full_INT_20150131.txt'
WITH DELIMITER '~';
CSV HEADER;


COPY curr_stated_relationship_f(id, effectivetime, active, moduleid, sourceid, destinationid, relationshipgroup, typeid,  characteristictypeid, modifierid)
FROM '/Users/enricopirani/download/RF2Release/Full/Terminology/sct2_StatedRelationship_Full_INT_20150131.txt'  
WITH DELIMITER '~';
CSV HEADER;

COPY curr_langrefset_f(id, effectivetime, active, moduleid, refsetid, referencedcomponentid, acceptabilityid)
FROM '/Users/enricopirani/download/RF2Release/Full/Refset/Language/der2_cRefset_LanguageFull-en_INT_20150131.txt' 
WITH DELIMITER '~';
CSV HEADER;

COPY curr_associationrefset_d(id, effectivetime, active, moduleid, refsetid, referencedcomponentid, targetcomponentid)
FROM '/Users/enricopirani/download/RF2Release/Full/Refset/Content/der2_cRefset_AssociationReferenceFull_INT_20150131.txt'
WITH DELIMITER '~';
CSV HEADER;


COPY curr_attributevaluerefset_f(id, effectivetime, active, moduleid, refsetid, referencedcomponentid, valueid)
FROM '/Users/enricopirani/download/RF2Release/Full/Refset/Content/der2_cRefset_AttributeValueFull_INT_20150131.txt'
WITH DELIMITER '~';
CSV HEADER;

COPY curr_simplemaprefset_f(id, effectivetime, active, moduleid, refsetid,  referencedcomponentid, maptarget);
FROM '/Users/enricopirani/download/RF2Release/Full/Refset/Crossmap/der2_sRefset_SimpleMapFull_INT_20150131.txt'
WITH DELIMITER '~';
CSV HEADER;

COPY curr_simplerefset_f(id, effectivetime, active, moduleid, refsetid, referencedcomponentid)
FROM '/Users/enricopirani/download/RF2Release/Full/Refset/Content/der2_Refset_SimpleFull_INT_20150131.txt' 
WITH DELIMITER '~';
CSV HEADER;

COPY curr_complexmaprefset_f(id, effectivetime, active, moduleid, refsetid, referencedcomponentid, mapGroup, mapPriority, mapRule,  mapAdvice, mapTarget, correlationId)
FROM '/Users/enricopirani/download/RF2Release/Full/Refset/Map/der2_iissscRefset_ComplexMapFull_INT_20150131.txt'
WITH DELIMITER '~';
CSV HEADER;

And loading the sql in snomedct db

~/download/SNOMED-CT-Database-master%=> psql -d snomedct -f load-postgresql.sql

I got this errors

LINE 3: use snomedct;
        ^
psql:load-postgresql.sql:8: ERROR:  relation "curr_concept_f" does not exist
psql:load-postgresql.sql:9: ERROR:  syntax error at or near "CSV"
LINE 1: CSV HEADER;
        ^
psql:load-postgresql.sql:14: ERROR:  relation "curr_description_f" does not exist
psql:load-postgresql.sql:15: ERROR:  syntax error at or near "CSV"
LINE 1: CSV HEADER;
        ^
psql:load-postgresql.sql:20: ERROR:  relation "curr_textdefinition_f" does not exist
psql:load-postgresql.sql:21: ERROR:  syntax error at or near "CSV"
LINE 1: CSV HEADER;
        ^
psql:load-postgresql.sql:26: ERROR:  relation "curr_relationship_f" does not exist
psql:load-postgresql.sql:27: ERROR:  syntax error at or near "CSV"
LINE 1: CSV HEADER;
        ^
psql:load-postgresql.sql:32: ERROR:  relation "curr_stated_relationship_f" does not exist
psql:load-postgresql.sql:33: ERROR:  syntax error at or near "CSV"
LINE 1: CSV HEADER;
        ^
psql:load-postgresql.sql:37: ERROR:  relation "curr_langrefset_f" does not exist
psql:load-postgresql.sql:38: ERROR:  syntax error at or near "CSV"
LINE 1: CSV HEADER;
        ^
psql:load-postgresql.sql:42: ERROR:  relation "curr_associationrefset_d" does not exist
psql:load-postgresql.sql:43: ERROR:  syntax error at or near "CSV"
LINE 1: CSV HEADER;
        ^
psql:load-postgresql.sql:48: ERROR:  relation "curr_attributevaluerefset_f" does not exist
psql:load-postgresql.sql:49: ERROR:  syntax error at or near "CSV"
LINE 1: CSV HEADER;
        ^
psql:load-postgresql.sql:51: ERROR:  syntax error at or near ";"
LINE 1: ...ive, moduleid, refsetid,  referencedcomponentid, maptarget);
                                                                      ^
psql:load-postgresql.sql:53: ERROR:  syntax error at or near "FROM"
LINE 1: FROM '/Users/enricopirani/download/RF2Release/Full/Refset/Cr...
        ^
psql:load-postgresql.sql:54: ERROR:  syntax error at or near "CSV"
LINE 1: CSV HEADER;
        ^
psql:load-postgresql.sql:58: ERROR:  relation "curr_simplerefset_f" does not exist
psql:load-postgresql.sql:59: ERROR:  syntax error at or near "CSV"
LINE 1: CSV HEADER;
        ^
psql:load-postgresql.sql:63: ERROR:  relation "curr_complexmaprefset_f" does not exist
psql:load-postgresql.sql:64: ERROR:  syntax error at or near "CSV"
LINE 1: CSV HEADER;

[rorydavidson]

Hi,
the error shows that it can't find the files. Where have you extracted the SNOMED CT release to?

[Enrico68]

Hi,

I've re-extracted the SnomedCT_RF2Release_INT_20150131.zip in /Users/enricopirani/download/SnomedCT_RF2Release_INT_20150131and change the location in load-postgresql.sql :

/* loads the SNOMED CT 'Full' release - replace filenames with relevant locations of base SNOMED CT release files*/
/* Filenames may need to change depending on the release you wish to upload, currently set to January 2015 release */

use snomedct;

COPY curr_concept_f(id, effectivetime, active, moduleid, definitionstatusid) 
FROM '/Users/enricopirani/download/SnomedCT_RF2Release_INT_20150131/Full/Terminology/sct2_Concept_Full_INT_20150131.txt' 
WITH DELIMITER '~';
CSV HEADER;


COPY curr_description_f(id, effectivetime, active, moduleid, conceptid, languagecode, typeid, term, casesignificanceid)
FROM '/Users/enricopirani/download/SnomedCT_RF2Release_INT_20150131/Full/Terminology/sct2_Description_Full-en_INT_20150131.txt' 
WITH DELIMITER '~';
CSV HEADER;


COPY curr_textdefinition_f(id, effectivetime, active, moduleid, conceptid, languagecode, typeid, term, casesignificanceid)
FROM '/Users/enricopirani/download/SnomedCT_RF2Release_INT_20150131/Full/Terminology/sct2_TextDefinition_Full-en_INT_20150131.txt'
WITH DELIMITER '~';
CSV HEADER;


COPY curr_relationship_f(id, effectivetime, active, moduleid, sourceid, destinationid, relationshipgroup, typeid,characteristictypeid, modifierid)
FROM '/Users/enricopirani/download/SnomedCT_RF2Release_INT_20150131/Full/Terminology/sct2_Relationship_Full_INT_20150131.txt'
WITH DELIMITER '~';
CSV HEADER;


COPY curr_stated_relationship_f(id, effectivetime, active, moduleid, sourceid, destinationid, relationshipgroup, typeid,  characteristictypeid, modifierid)
FROM '/Users/enricopirani/download/SnomedCT_RF2Release_INT_20150131/Full/Terminology/sct2_StatedRelationship_Full_INT_20150131.txt'  
WITH DELIMITER '~';
CSV HEADER;

COPY curr_langrefset_f(id, effectivetime, active, moduleid, refsetid, referencedcomponentid, acceptabilityid)
FROM '/Users/enricopirani/download/SnomedCT_RF2Release_INT_20150131/Full/Refset/Language/der2_cRefset_LanguageFull-en_INT_20150131.txt' 
WITH DELIMITER '~';
CSV HEADER;

COPY curr_associationrefset_d(id, effectivetime, active, moduleid, refsetid, referencedcomponentid, targetcomponentid)
FROM '/Users/enricopirani/download/SnomedCT_RF2Release_INT_20150131/Full/Refset/Content/der2_cRefset_AssociationReferenceFull_INT_20150131.txt'
WITH DELIMITER '~';
CSV HEADER;


COPY curr_attributevaluerefset_f(id, effectivetime, active, moduleid, refsetid, referencedcomponentid, valueid)
FROM '/Users/enricopirani/download/SnomedCT_RF2Release_INT_20150131/Full/Refset/Content/der2_cRefset_AttributeValueFull_INT_20150131.txt'
WITH DELIMITER '~';
CSV HEADER;

COPY curr_simplemaprefset_f(id, effectivetime, active, moduleid, refsetid,  referencedcomponentid, maptarget);
FROM '/Users/enricopirani/download/SnomedCT_RF2Release_INT_20150131/Full/Refset/Crossmap/der2_sRefset_SimpleMapFull_INT_20150131.txt'
WITH DELIMITER '~';
CSV HEADER;

COPY curr_simplerefset_f(id, effectivetime, active, moduleid, refsetid, referencedcomponentid)
FROM '/Users/enricopirani/download/SnomedCT_RF2Release_INT_20150131/Full/Refset/Content/der2_Refset_SimpleFull_INT_20150131.txt' 
WITH DELIMITER '~';
CSV HEADER;

COPY curr_complexmaprefset_f(id, effectivetime, active, moduleid, refsetid, referencedcomponentid, mapGroup, mapPriority, mapRule,  mapAdvice, mapTarget, correlationId)
FROM '/Users/enricopirani/download/SnomedCT_RF2Release_INT_20150131/Full/Refset/Map/der2_iissscRefset_ComplexMapFull_INT_20150131.txt'
WITH DELIMITER '~';
CSV HEADER;

But still the same errors :

psql -d snomedct -f load-postgresql.sql

psql:load-postgresql.sql:4: ERROR:  syntax error at or near "use"
LINE 3: use snomedct;
        ^
psql:load-postgresql.sql:8: ERROR:  value too long for type character varying(18)
CONTEXT:  COPY curr_concept_f, line 1, column id: "id   effectiveTime   active  moduleId    definitionStatusId"
psql:load-postgresql.sql:9: ERROR:  syntax error at or near "CSV"
LINE 1: CSV HEADER;
        ^
psql:load-postgresql.sql:14: ERROR:  value too long for type character varying(18)
CONTEXT:  COPY curr_description_f, line 1, column id: "id   effectiveTime   active  moduleId    conceptId   languageCode    typeId  term    caseSignificanceId"
psql:load-postgresql.sql:15: ERROR:  syntax error at or near "CSV"
LINE 1: CSV HEADER;
        ^
psql:load-postgresql.sql:20: ERROR:  value too long for type character varying(18)
CONTEXT:  COPY curr_textdefinition_f, line 1, column id: "id    effectiveTime   active  moduleId    conceptId   languageCode    typeId  term    caseSignificanceId"
psql:load-postgresql.sql:21: ERROR:  syntax error at or near "CSV"
LINE 1: CSV HEADER;
        ^
psql:load-postgresql.sql:26: ERROR:  value too long for type character varying(18)
CONTEXT:  COPY curr_relationship_f, line 1, column id: "id  effectiveTime   active  moduleId    sourceId    destinationId   relationshipGroup   typeId  characteristicTypeI..."
psql:load-postgresql.sql:27: ERROR:  syntax error at or near "CSV"
LINE 1: CSV HEADER;
        ^
psql:load-postgresql.sql:32: ERROR:  relation "curr_stated_relationship_f" does not exist
psql:load-postgresql.sql:33: ERROR:  syntax error at or near "CSV"
LINE 1: CSV HEADER;
        ^
psql:load-postgresql.sql:37: ERROR:  value too long for type character varying(18)
CONTEXT:  COPY curr_langrefset_f, line 1, column id: "id    effectiveTime   active  moduleId    refsetId    referencedComponentId   acceptabilityId"
psql:load-postgresql.sql:38: ERROR:  syntax error at or near "CSV"
LINE 1: CSV HEADER;
        ^
psql:load-postgresql.sql:42: ERROR:  value too long for type character varying(18)
CONTEXT:  COPY curr_associationrefset_d, line 1, column id: "id effectiveTime   active  moduleId    refsetId    referencedComponentId   targetComponentId"
psql:load-postgresql.sql:43: ERROR:  syntax error at or near "CSV"
LINE 1: CSV HEADER;
        ^
psql:load-postgresql.sql:48: ERROR:  value too long for type character varying(18)
CONTEXT:  COPY curr_attributevaluerefset_f, line 1, column id: "id  effectiveTime   active  moduleId    refsetId    referencedComponentId   valueId"
psql:load-postgresql.sql:49: ERROR:  syntax error at or near "CSV"
LINE 1: CSV HEADER;
        ^
psql:load-postgresql.sql:51: ERROR:  syntax error at or near ";"
LINE 1: ...ive, moduleid, refsetid,  referencedcomponentid, maptarget);
                                                                      ^
psql:load-postgresql.sql:53: ERROR:  syntax error at or near "FROM"
LINE 1: FROM '/Users/enricopirani/download/SnomedCT_RF2Release_INT_2...
        ^
psql:load-postgresql.sql:54: ERROR:  syntax error at or near "CSV"
LINE 1: CSV HEADER;
        ^
psql:load-postgresql.sql:58: ERROR:  value too long for type character varying(18)
CONTEXT:  COPY curr_simplerefset_f, line 1, column id: "id  effectiveTime   active  moduleId    refsetId    referencedComponentId"
psql:load-postgresql.sql:59: ERROR:  syntax error at or near "CSV"
LINE 1: CSV HEADER;
        ^
psql:load-postgresql.sql:63: ERROR:  value too long for type character varying(18)
CONTEXT:  COPY curr_complexmaprefset_f, line 1, column id: "id  effectiveTime   active  moduleId    refsetId    referencedComponentId   mapGroup    mapPriority mapRule mapAdvi..."
psql:load-postgresql.sql:64: ERROR:  syntax error at or near "CSV"
LINE 1: CSV HEADER;
        ^

[jmpolitzer]

@Enrico68 I'm running into the same issue. Were you ever able to solve this?

[rorydavidson]

The repo has been updated now. Please have a go again.