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_maxmind FROM '/home/www/geo/DATA/MAXMIND.com/cities_no_header.txt' CSV quote as ''''; -- Adrian Klaver [EMAIL PROTECTED] ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq