Message-ID: <471EF003.7030707@autoledgers.com.au>
Date: Wed, 24 Oct 2007 15:10:59 +0800
From: Paul Lambert <paul.lambert@autoledgers.com.au>
User-Agent: Thunderbird 2.0.0.6 (Windows/20070728)
MIME-Version: 1.0
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: request for help with COPY syntax
References: <200710231019.07833.pgsql-list@nullmx.com>
In-Reply-To: <200710231019.07833.pgsql-list@nullmx.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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