From: "Chuck D." <pgsql-list@nullmx.com>
Reply-To: pgsql-list@nullmx.com
To: pgsql-sql@postgresql.org
Subject: request for help with COPY syntax
Date: Tue, 23 Oct 2007 10:19:07 -0600
User-Agent: KMail/1.9.6
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200710231019.07833.pgsql-list@nullmx.com>

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.