Re: request for help with COPY syntax

On Tuesday 23 October 2007 9:19 am, 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 got it to work with your sample data by using the COPY command as follows:
COPY geo.orig_city_maxmind
FROM '/home/www/geo/DATA/MAXMIND.com/cities_no_header.txt'
CSV quote as '''';

--
Adrian Klaver
aklaver(at)comcast(dot)net