Chuck D. wrote:
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.
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
I get around this problem with my data loads by specifying some other
arbitrary character that I know won't appear in the data as the quote
character.
Eg QUOTE E'\f' will specify form feed as the quote character, ergo any
data with double or single quotes will be loaded with those quote
characters in the string.
Something similar may help with your case.
--
Paul Lambert
Database Administrator
AutoLedgers
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
