From: Adrian Klaver <aklaver@comcast.net>
To: pgsql-sql@postgresql.org,
 pgsql-list@nullmx.com
Subject: Re: request for help with COPY syntax
Date: Tue, 23 Oct 2007 19:51:18 -0700
User-Agent: KMail/1.9.1
References: <200710231019.07833.pgsql-list@nullmx.com>
In-Reply-To: <200710231019.07833.pgsql-list@nullmx.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200710231951.18423.aklaver@comcast.net>

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@comcast.net