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

From Adrian Klaver
Subject Re: request for help with COPY syntax
Date
Msg-id 200710231951.18423.aklaver@comcast.net
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
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_maxmindFROM '/home/www/geo/DATA/MAXMIND.com/cities_no_header.txt'CSV quote as '''';

-- 
Adrian Klaver
aklaver@comcast.net


pgsql-sql by date:

Previous
From: Tom Lane
Date:
Subject: Re: Quick question re foreign keys.
Next
From: Paul Lambert
Date:
Subject: Re: Quick question re foreign keys.