Thread: running a query file of 42MB
I'm trying to run query generated by an import script. postgresql 8
onder windows XP.
the file is about 42MB, i've opened this file by pgAdminIII and start
the query, it inserts data into 3 tables and after makes some update.
the time is too much and it hangs up without any commit.
any body know another methode to run the query.
i've tried with the command line psql, but get errors because the file
is like:
insert into table1 (pkey) values ('xxx');
insert into table2 (pkey,field) values ('yyy',(select OID from table1
where pkey='xxx'));
update table1 set field1='a', field2='b', field3='c' where pkey = 'xxx';
insert into table1 (pkey) values ('xxx');
insert into table2 (pkey,field) values ('zzz',(select OID from table1
where pkey='xxx'));
update table1 set field1='u', field2='v', field3='q' where pkey = 'xxx';
.
.
.etc
the reason to do so is to get the OID for each record inserted into the
table1 and use that in the table2.
i've limited the query to 2 tables but there are more tables and also
more related fields from the same table.
in the psql i've tried this:
>psql -d dbname -u username -f file.sql
i've also tried with
>psql -d dbname -u username < file.sql
but get the error, pkey violatde. should i use a commit between each
record ?
regards,
kasem,
****************************************************************************
Disclaimer:
This electronic transmission and any files attached to it are strictly
confidential and intended solely for the addressee. If you are not
the intended addressee, you must not disclose, copy or take any
action in reliance of this transmission. If you have received this
transmission in error, please notify the sender by return and delete
the transmission. Although the sender endeavors to maintain a
computer virus free network, the sender does not warrant that this
transmission is virus-free and will not be liable for any damages
resulting from any virus transmitted.
Thank You.
****************************************************************************
dear all,
it is okay,
the import by psql is faster and quit, it is running now.
i've found an error in the query, it was the decimal separator that must be "." and not ",".
now it's working.
thx to all
kasem,
-----Original Message-----
From: Nefnifi, Kasem
Sent: Tuesday, December 28, 2004 2:28 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] running a query file of 42MB
I'm trying to run query generated by an import script. postgresql 8
onder windows XP.
the file is about 42MB, i've opened this file by pgAdminIII and start
the query, it inserts data into 3 tables and after makes some update.
the time is too much and it hangs up without any commit.
any body know another methode to run the query.
i've tried with the command line psql, but get errors because the file
is like:
insert into table1 (pkey) values ('xxx');
insert into table2 (pkey,field) values ('yyy',(select OID from table1
where pkey='xxx'));
update table1 set field1='a', field2='b', field3='c' where pkey = 'xxx';
insert into table1 (pkey) values ('xxx');
insert into table2 (pkey,field) values ('zzz',(select OID from table1
where pkey='xxx'));
update table1 set field1='u', field2='v', field3='q' where pkey = 'xxx';
.
.
.etc
the reason to do so is to get the OID for each record inserted into the
table1 and use that in the table2.
i've limited the query to 2 tables but there are more tables and also
more related fields from the same table.
in the psql i've tried this:
>psql -d dbname -u username -f file.sql
i've also tried with
>psql -d dbname -u username < file.sql
but get the error, pkey violatde. should i use a commit between each
record ?
regards,
kasem,
****************************************************************************
Disclaimer:
This electronic transmission and any files attached to it are strictly
confidential and intended solely for the addressee. If you are not
the intended addressee, you must not disclose, copy or take any
action in reliance of this transmission. If you have received this
transmission in error, please notify the sender by return and delete
the transmission. Although the sender endeavors to maintain a
computer virus free network, the sender does not warrant that this
transmission is virus-free and will not be liable for any damages
resulting from any virus transmitted.
Thank You.
****************************************************************************
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
Don't use OIDs for primary keys, please, please.
Use a Sequence instead, and replace your SELECT oid by SELECT
currval('sequence_name')
On Tue, 28 Dec 2004 15:44:56 +0100, Nefnifi, Kasem
<Kasem.Nefnifi@atosorigin.com> wrote:
> dear all,
>
> it is okay,
> the import by psql is faster and quit, it is running now.
> i've found an error in the query, it was the decimal separator that must
> be "." and not ",".
> now it's working.
> thx to all
> kasem,
>
> -----Original Message-----
> From: Nefnifi, Kasem
> Sent: Tuesday, December 28, 2004 2:28 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] running a query file of 42MB
>
>
>
> I'm trying to run query generated by an import script. postgresql 8
> onder windows XP.
> the file is about 42MB, i've opened this file by pgAdminIII and start
> the query, it inserts data into 3 tables and after makes some update.
> the time is too much and it hangs up without any commit.
>
> any body know another methode to run the query.
>
> i've tried with the command line psql, but get errors because the file
> is like:
>
> insert into table1 (pkey) values ('xxx');
> insert into table2 (pkey,field) values ('yyy',(select OID from table1
> where pkey='xxx'));
> update table1 set field1='a', field2='b', field3='c' where pkey = 'xxx';
>
> insert into table1 (pkey) values ('xxx');
> insert into table2 (pkey,field) values ('zzz',(select OID from table1
> where pkey='xxx'));
> update table1 set field1='u', field2='v', field3='q' where pkey = 'xxx';
> .
> .
> .etc
>
> the reason to do so is to get the OID for each record inserted into the
> table1 and use that in the table2.
> i've limited the query to 2 tables but there are more tables and also
> more related fields from the same table.
>
> in the psql i've tried this:
>> psql -d dbname -u username -f file.sql
> i've also tried with
>> psql -d dbname -u username < file.sql
>
> but get the error, pkey violatde. should i use a commit between each
> record ?
>
> regards,
> kasem,
> ****************************************************************************
> Disclaimer:
> This electronic transmission and any files attached to it are strictly
> confidential and intended solely for the addressee. If you are not
> the intended addressee, you must not disclose, copy or take any
> action in reliance of this transmission. If you have received this
> transmission in error, please notify the sender by return and delete
> the transmission. Although the sender endeavors to maintain a
> computer virus free network, the sender does not warrant that this
> transmission is virus-free and will not be liable for any damages
> resulting from any virus transmitted.
> Thank You.
> ****************************************************************************
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
that is right Pierre, i use an other field nl. id that is incremented auto. Best Regards / Vriendelijke Groeten / Salutations Distinguées / Freundliche Grüße !!! Kasem NEFNIFI AtosOrigin Belgium N.V. Minervastraat 7 1930 Zaventem (Belgium) Tel : +32(0)2 712 28 30 Fax : +32(0)2 712 28 63 GSM : +32 495 25 12 33 Email : kasem.nefnifi@atosorigin.com <mailto:kasem.nefnifi@atosorigin.com> www.atosorigin.com <http://www.atosorigin.com> -----Original Message----- From: Pierre-Frédéric Caillaud [mailto:lists@boutiquenumerique.com] Sent: Tuesday, December 28, 2004 4:10 PM To: Nefnifi, Kasem; pgsql-general@postgresql.org Subject: Re: [GENERAL] running a query file of 42MB Don't use OIDs for primary keys, please, please. Use a Sequence instead, and replace your SELECT oid by SELECT currval('sequence_name') On Tue, 28 Dec 2004 15:44:56 +0100, Nefnifi, Kasem <Kasem.Nefnifi@atosorigin.com> wrote: > dear all, > > it is okay, > the import by psql is faster and quit, it is running now. > i've found an error in the query, it was the decimal separator that must > be "." and not ",". > now it's working. > thx to all > kasem, > > -----Original Message----- > From: Nefnifi, Kasem > Sent: Tuesday, December 28, 2004 2:28 PM > To: pgsql-general@postgresql.org > Subject: [GENERAL] running a query file of 42MB > > > > I'm trying to run query generated by an import script. postgresql 8 > onder windows XP. > the file is about 42MB, i've opened this file by pgAdminIII and start > the query, it inserts data into 3 tables and after makes some update. > the time is too much and it hangs up without any commit. > > any body know another methode to run the query. > > i've tried with the command line psql, but get errors because the file > is like: > > insert into table1 (pkey) values ('xxx'); > insert into table2 (pkey,field) values ('yyy',(select OID from table1 > where pkey='xxx')); > update table1 set field1='a', field2='b', field3='c' where pkey = 'xxx'; > > insert into table1 (pkey) values ('xxx'); > insert into table2 (pkey,field) values ('zzz',(select OID from table1 > where pkey='xxx')); > update table1 set field1='u', field2='v', field3='q' where pkey = 'xxx'; > . > . > .etc > > the reason to do so is to get the OID for each record inserted into the > table1 and use that in the table2. > i've limited the query to 2 tables but there are more tables and also > more related fields from the same table. > > in the psql i've tried this: >> psql -d dbname -u username -f file.sql > i've also tried with >> psql -d dbname -u username < file.sql > > but get the error, pkey violatde. should i use a commit between each > record ? > > regards, > kasem, > **************************************************************************** > Disclaimer: > This electronic transmission and any files attached to it are strictly > confidential and intended solely for the addressee. If you are not > the intended addressee, you must not disclose, copy or take any > action in reliance of this transmission. If you have received this > transmission in error, please notify the sender by return and delete > the transmission. Although the sender endeavors to maintain a > computer virus free network, the sender does not warrant that this > transmission is virus-free and will not be liable for any damages > resulting from any virus transmitted. > Thank You. > **************************************************************************** > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > **************************************************************************** Disclaimer: This electronic transmission and any files attached to it are strictly confidential and intended solely for the addressee. If you are not the intended addressee, you must not disclose, copy or take any action in reliance of this transmission. If you have received this transmission in error, please notify the sender by return and delete the transmission. Although the sender endeavors to maintain a computer virus free network, the sender does not warrant that this transmission is virus-free and will not be liable for any damages resulting from any virus transmitted. Thank You. ****************************************************************************