From: Chuck D. <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
Message-ID: <200710231019.07833.pgsql-list@nullmx.com> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pgsql-list@nullmx.com
Subject: Re: request for help with COPY syntax
In-Reply-To: <200710231019.07833.pgsql-list@nullmx.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox