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

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.


pgsql-sql by date:

Previous
From: Erik Jones
Date:
Subject: Re: [PERFORM] two queryes in a single tablescan
Next
From: Andrew Sullivan
Date:
Subject: Re: request for help with COPY syntax