[SQL] request for help with COPY syntax
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
Re: [SQL] request for help with COPY syntax
On Tue, Oct 23, 2007 at 10:19:07AM -0600, Chuck D. wrote: > 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. Ugh. I think I would normalise the data before COPYing, myself. This is a generally good practice for importing data: too much intelligence in the import stage itself can cause unexpected side effects and debugging pain. Better to put an extra step in that ensures the data is all marked up consistently on the way into the import step. A -- Andrew Sullivan | [EMAIL PROTECTED] "The year's penultimate month" is not in truth a good way of saying November. --H.W. Fowler ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] request for help with COPY syntax
> -Mensaje original- > De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > En nombre de Chuck D. > > 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. > Hi Chuck, Do you need those characters in your table? If not I think you will be better off preprocessing the data before running copy. Replacing those " for ' or directly removing them is quite simple if you are working in Unix, actually it should be quite simple in any operating system. Regards, Fernando ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Quick question re foreign keys.
I have a table where one column references by foreign key a column from another table. However, the column in this first table does not always contain data which results in a not-null constraint violation when I attempt an insert. My question therefore is, is it possible to create a foreign key that is conditional, i.e. only enforce the foreign key where the value in that table is not null. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Quick question re foreign keys.
> My question therefore is, is it possible to create a foreign key that is > conditional, i.e. only enforce the foreign key where the value in that > table is not null. My understanding from reading previous threads on this topic is the answer is no, however you could make your own pseudo-foreign key using triggers to do the same job, but only when your column is not null. Not sure of the performance impact of doing this though - someone else may be able to advise pros and cons in more detail. Cheers, ~p THINK BEFORE YOU PRINT - Save paper if you don't really need to print this ***Confidentiality and Privilege Notice*** The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments e-mail. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Quick question re foreign keys.
Paul Lambert <[EMAIL PROTECTED]> writes: > I have a table where one column references by foreign key a column from > another table. However, the column in this first table does not always > contain data which results in a not-null constraint violation when I > attempt an insert. > My question therefore is, is it possible to create a foreign key that is > conditional, i.e. only enforce the foreign key where the value in that > table is not null. If I'm understanding you correctly, the problem is not the foreign key, it's that you marked the column NOT NULL. A foreign key constraint by itself will allow a NULL in the referencing column to pass. You choose whether you want to allow that or not by separately applying a NOT NULL constraint or not. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] request for help with COPY syntax
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
Re: [SQL] Quick question re foreign keys.
Tom Lane wrote: If I'm understanding you correctly, the problem is not the foreign key, it's that you marked the column NOT NULL. A foreign key constraint by itself will allow a NULL in the referencing column to pass. You choose whether you want to allow that or not by separately applying a NOT NULL constraint or not. regards, tom lane It's marked not null as a result of being part of the primary key for that table which I can't really get around. I can get away with not having the foreign key though, so I'll have to go down that path. Cheers, P. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Quick question re foreign keys.
On Wed, 24 Oct 2007 11:00:47 +0800 Paul Lambert <[EMAIL PROTECTED]> wrote: > It's marked not null as a result of being part of the primary key for > that table which I can't really get around. > > I can get away with not having the foreign key though, so I'll have to > go down that path. It can't be the primary key and have NULLs. It sounds to me like you have a design problem somewhere. -- D'Arcy J.M. Cain <[EMAIL PROTECTED]> | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
