field names don't seem to be quoted propertly.

[timuckun]

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.

[lukasmartinelli]

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.

[timuckun]

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.

[lukasmartinelli]

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.

[timuckun]

Just FYI. If you put the field names in double quotes postgres doesn't have problems.

[frodrigo]

Obviously you need to quote the field name.