Re: [SQL] request for help with COPY syntax
On October 23, 2007 08:51:18 pm you wrote: 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 ; I see what you are after and you solved the syntax problem I was having, but now I get the same error on a different line: ad,l'aldosa,L'Aldosa,02,42.583,1.633 I guess it considers that line quoted now. In other words, some lines may have both single and double quotes involved, like this: kz,otdeleniye imeni dvadtsat' vtorogo partsyezda,Otdeleniye Imeni Dvadtsat' Vtorogo Partsyezda,10,41.47,69.1280556 Is there any way to tell Postgresql that a CSV file has no quotes around each field, and that each field may have single or double quotes or both? ---(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
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,kruhavyetskalinina,KruhavyetsKalinina,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 ---(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] Quick question re foreign keys.
D'Arcy J.M. Cain skrev: 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. Well, I have a couple of times had the need to have a primary key/uniqueness constraint with one column nullable (indicating Not Applicable). The problem is that we have only one NULL, which for comparison purposes is interpreted as Not Known. Nis ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] request for help with COPY syntax
In article [EMAIL PROTECTED], Chuck D. [EMAIL PROTECTED] writes: On October 23, 2007 08:51:18 pm you wrote: 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 ; I see what you are after and you solved the syntax problem I was having, but now I get the same error on a different line: ad,l'aldosa,L'Aldosa,02,42.583,1.633 I guess it considers that line quoted now. In other words, some lines may have both single and double quotes involved, like this: kz,otdeleniye imeni dvadtsat' vtorogo partsyezda,Otdeleniye Imeni Dvadtsat' Vtorogo Partsyezda,10,41.47,69.1280556 Is there any way to tell Postgresql that a CSV file has no quotes around each field, and that each field may have single or double quotes or both? Can't you just say COPY geo.orig_city_maxmind FROM '/home/www/geo/DATA/MAXMIND.com/cities_no_header.txt' DELIMITER ',' ? ---(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
[SQL] get only rows for latest version of contents
Hi everybody.
I have a table like that (i simplified it):
CREATE TABLE business {
idnode integer not null,
version_no integer,
c1 text,
c2 text,
c3 text
}
With a unique index in (idnode,version_no).
This table records many version from contents identified by idnode where
texts may be different.
So i can have:
Idnode | version_no | c1| c2| c3
111| 2 | foo1 | foo2 | foo3
111| 1 | fee1 | foo2 | foo3
111| null | fee1 | fee2 | fee3
222| null | too1 | too2 | too3
333| 1 | xoo1 | xoo2 | xoo3
333| null | yoo1 | yoo2 | yee3
I want to select all columns but only for last (greatest) version of each
content. So I want a result like:
Idnode | version_no | c1| c2| c3
111| 2 | foo1 | foo2 | foo3
222| null | too1 | too2 | too3
333| 1 | xoo1 | xoo2 | xoo3
If i do:
SELECT idnode, max(version_no) FROM business
GROUP BY idnode ORDER BY idnode;
I get effectively only last version:
Idnode | version_no
111| 2
222| null
333| 1
But as soon that i want to get texts, I don't know how to build the SQL.
In each SQL i tested i've been forced to put text column in a group by since
i used aggregate for version_no:
SELECT idnode, max(version_no), c1, c2, c3 FROM BUSINESS
GROUP BY idnode, c1, c2, c3 ORDER BY idnode;
But with that SQL, because of the group by and different values in text i
get
Idnode | version_no | c1| c2| c3
111| 2 | foo1 | foo2 | foo3
111| 1 | fee1 | foo2 | foo3
111| null | fee1 | fee2 | fee3
222| null | too1 | too2 | too3
333| 1 | xoo1 | xoo2 | xoo3
333| null | yoo1 | yoo2 | yee3
As we can't do aggregate in join neither in where, i can't get what i want.
Anybody could help me to build proper SQL ?
Thx for your answers.
Sébastien.
---(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.
Forgive my butting in, but frankly, most of the times, whenever I find myself in a very 'exceptional problem' such as this one, I always end up questioning the basic design due to which I am stuck in the first place. Paul, it seems that probably there is a basic design issue here. All the best :) Robins On 10/24/07, Paul Lambert [EMAIL PROTECTED] wrote: Paul Lambert 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. Cheers, P. Ignore this whole thread actually. I need to rethink some of my design. -- Paul Lambert Database Administrator AutoLedgers ---(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] Quick question re foreign keys.
On Wed, 24 Oct 2007 09:43:10 +0200 Nis Jørgensen [EMAIL PROTECTED] wrote: Well, I have a couple of times had the need to have a primary key/uniqueness constraint with one column nullable (indicating Not Applicable). The problem is that we have only one NULL, which for comparison purposes is interpreted as Not Known. Of course. Happens all the time. However, UNIQUE and PRIMARY are not the same thing. PRIMARY implies that the column uniquely and definitively identifies the row. If you have NULLs in the column than it does not meet the criteria. Here are the rules for primary key taken from http://articles.techrepublic.com.com/5100-22-1045050.html. - The primary key must uniquely identify each record. - A record?s primary-key value can?t be null. - The primary key-value must exist when the record is created. - The primary key must remain stable?you can?t change the primary-key field(s). - The primary key must be compact and contain the fewest possible attributes. - The primary-key value can?t be changed. -- 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 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] ERROR: failed to re-find parent key in pk_ep07
When i was vacuum the database, the vacuum if failed. And I get this error. Any ideas an to fix this? ERROR: failed to re-find parent key in pk_ep07 Thanks before. Note : EP07 is name of tables. -- He who is quick to become angry will commit folly, and a crafty man is hated __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [SQL] ERROR: failed to re-find parent key in pk_ep07
Otniel Michael [EMAIL PROTECTED] writes: When i was vacuum the database, the vacuum if failed. And I get this error. Any ideas an to fix this? ERROR: failed to re-find parent key in pk_ep07 Update to a newer PG version, possibly? This symptom has been seen before... regards, tom lane ---(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
