From: Paul Lambert <paul.lambert@autoledgers.com.au>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: request for help with COPY syntax
Date: Wed, 24 Oct 2007 15:10:59 +0800
Message-ID: <471EF003.7030707@autoledgers.com.au> (raw)
In-Reply-To: <200710231019.07833.pgsql-list@nullmx.com>
References: <200710231019.07833.pgsql-list@nullmx.com>
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
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: paul.lambert@autoledgers.com.au
Subject: Re: request for help with COPY syntax
In-Reply-To: <471EF003.7030707@autoledgers.com.au>
* 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