Greetings everyone,
I'm having some trouble with COPY syntax.
I'm importing the cities data from MaxMind, but I run into errors when the
data adds a double quote inside a field.
The data is CSV, comma delimited, no quotes around fields, ISO-8859-1. I'm
using COPY with the defaults and setting client encoding to LATIN1.
The temporary table for importing looks like this:
Table "geo.orig_city_maxmind" Column | Type | Modifiers
-------------+-----------------------+-----------cc1 | character(2) |city | text
|accent_city | text |region | character(3) |latitude | character varying(18)
|longitude | character varying(18) |
The COPY command is:
COPY geo.orig_city_maxmind FROM '/home/www/geo/DATA/MAXMIND.com/cities_no_header.txt'
CSV;
Here is one error I get:
ERROR: value too long for type character(3)
CONTEXT: COPY orig_city_maxmind, line 281430, column region: "52.1438889"
Looking at line 281430 we see:
by,kruhavyetskalini"na,KruhavyetsKalini"na,02,52.1438889,31.6925
There are a couple " where I would expect to see ' instead. I see other lines
in the data that use both in a field.
I tried this with the earth-info.nga.mil data and I have a similar problem but
they are using newlines within a field and I can't figure out how to allow
them.
Anyone known how I can rewrite the COPY command to allow those " or ' within
the data? After a couple days I wasn't able to find any examples to help.
