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 parts"yezda,Otdeleniye Imeni Dvadtsat' Vtorogo Parts"yezda,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
On October 23, 2007 10:44:51 am you wrote: > 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 Greetings, I'm not sure if they are needed because I've never seen a double quote in a place name before. I don't believe they are errors though because there are more records that contain them. As well, some records have single and double quotes allowed within a record and this really messes things up. Any ideas? Should I consider removing them in favor of a single quote? If so, do you know how to do this with sed or similar? ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
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,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 ---(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 parts"yezda,Otdeleniye Imeni Dvadtsat' > Vtorogo Parts"yezda,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] get only rows for latest version of contents
Hi!
not quick mut works
select * from business b1
where b1.version_no = (SELECT max(version_no) FROM business b2.
where b2.idnode = b1.idnode
)
If you want to make this quiry faster du a regular join
select b1.*
from business b1,
(SELECT max(version_no) FROM business b2.
where b2.idnode = b1.idnode
) as b2
where b1.idnode = b2.idnode
and b1.version_no = b2.version_nr
Regards Chris
PS written without running any sql, maybe there are some syntax issues, but i
am shure you will figure these out :-)
On Wed, October 24, 2007 11:29 am, Sébastien Meudec wrote:
> 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
>
--
cu
Chris
Ist Ihr Browser Vista-kompatibel? Jetzt die neuesten
Browser-Versionen downloaden: http://www.gmx.net/de/go/browser
---(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 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] request for help with COPY syntax
> De: Chuck D. > > I'm not sure if they are needed because I've never seen a double quote in > a > place name before. I don't believe they are errors though because there > are > more records that contain them. As well, some records have single and > double > quotes allowed within a record and this really messes things up. > > Any ideas? Should I consider removing them in favor of a single quote? > If > so, do you know how to do this with sed or similar? > Well, hard to say what to do with those quotes without knowing if your query conditions for places will include them. I probably would replace them for an underscore or any other uniquely identifiable character in order to succeed with the copy, and keep the possibility to later decide if that underscore becomes again a quote or gets removed all together. If you would like to just remove single or double quotes you should do: sed "s/[\'\"]//g" file_with_quotes.txt > file_without_quotes.txt Say you want to replace quotes with a space, then: sed "s/[\'\"]/ /g" file_with_quotes.txt > file_without_quotes.txt Insert whatever you want to replace the quotes between the 2nd and 3rd bar (/). Regards, Fernando. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
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
Re: [SQL] get only rows for latest version of contents
Thx a lot Chris.
In fact the correct SQL was (rewritten with inner join because of it is
required by my api):
select b1.*
from business b1
inner join (select idnode,max(version_no) as version_no from business
group by idnode) as b2
on b1.idnode = b2.idnode and
(b1.version_no = b2.version_no or b2.version_no is null)
Regards,
Seb.
> -Message d'origine-
> De : [EMAIL PROTECTED] [mailto:pgsql-sql-
> [EMAIL PROTECTED] De la part de Christian Kindler
> Envoyé : mercredi 24 octobre 2007 11:55
> À : Sébastien Meudec
> Cc : pgsql-sql@postgresql.org
> Objet : Re: [SQL] get only rows for latest version of contents
>
> Hi!
>
> not quick mut works
>
> select * from business b1
> where b1.version_no = (SELECT max(version_no) FROM business b2.
> where b2.idnode = b1.idnode
>)
>
> If you want to make this quiry faster du a regular join
>
> select b1.*
> from business b1,
>(SELECT max(version_no) FROM business b2.
> where b2.idnode = b1.idnode
>) as b2
> where b1.idnode = b2.idnode
> and b1.version_no = b2.version_nr
>
> Regards Chris
>
> PS written without running any sql, maybe there are some syntax issues,
> but i am shure you will figure these out :-)
>
>
>
> On Wed, October 24, 2007 11:29 am, Sébastien Meudec wrote:
> > 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
> >
> --
> cu
> Chris
>
> Ist Ihr Browser Vista-kompatibel? Jetzt die neuesten
> Browser-Versionen downloaden: http://www.gmx.net/de/go/browser
>
> ---(end of broadcast)---
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
