Re: request for help with COPY syntax - Mailing list pgsql-sql

From Paul Lambert
Subject Re: request for help with COPY syntax
Date
Msg-id 471EF003.7030707@autoledgers.com.au
Whole thread Raw
In response to request for help with COPY syntax  ("Chuck D." <pgsql-list@nullmx.com>)
Responses Re: request for help with COPY syntax
List pgsql-sql
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



pgsql-sql by date:

Previous
From: "Chuck D."
Date:
Subject: Re: request for help with COPY syntax
Next
From: Nis Jørgensen
Date:
Subject: Re: Quick question re foreign keys.