pgfutter
pgfutter copied to clipboard
field names don't seem to be quoted propertly.
I have a csv file where one of the fields is called 'references". When I attempt to import I get this error.
pq: syntax error at or near "references"
I rename the field to be _references and it gives the same error but for the field "order". I fix that and it imports.
I presume this is because the name is not being quoted?
Thanks.
I have a csv file where one of the fields is called 'references". When I attempt to import I get this error.
pq: syntax error at or near "references"
I rename the field to be _references and it gives the same error but for the field "order". I fix that and it imports.
I presume this is because the name is not being quoted?
Thanks.
Can you give me the full CSV header? I try to strip away non alphanumeric characters in the columns to make them valid in PostgreSQL.
Here is the header
id,occurrenceID,basisOfRecord,modified,institutionCode,collectionCode,datasetName,informationWithheld,catalogNumber,_references,occurrenceRemarks,occurrenceDetails,recordedBy,establishmentMeans,eventDate,eventTime,verbatimEventDate,verbatimLocality,decimalLatitude,decimalLongitude,coordinateUncertaintyInMeters,countryCode,identificationID,dateIdentified,identificationRemarks,taxonID,scientificName,taxonRank,kingdom,phylum,class,_order,family,genus,license,rights,rightsHolder
notice that I put underscores in front of references and order in order to get it import.
I also noticed that it imported everything as text BTW.
I rename the field to be _references and it gives the same error but for the field "order". I fix that and it imports.
I get it now. :grinning: It is a bit unlucky: bothREFERENCES
and ORDER
are PostgreSQL keywords and cannot be used as column name...
I also noticed that it imported everything as text BTW.
We can not know if something is a number without parsing through the entire file and ensuring that something is a number. However this makes it impossible to pass records via STDIN and requires memory - that's why this kind of validation should be done in the database.
Importing in a import
schema and then migrate it over into public
schema (andnormalize in the process) is the recommended approach.
Just FYI. If you put the field names in double quotes postgres doesn't have problems.
Obviously you need to quote the field name.