Re: Newbie: PG8 and text file parsing

On Fri, 2005-02-18 at 20:34 -0500, Paul R wrote:
> I need some advice.. I am new to PG8 - I am working on the project
> that needs to load data daily to PostgreSQL from the text file.. the
> text file is 40MB comma delimited file with row consisting of 20-30
> fields and couple thousands of rows..

Hello,

We had to do something similar once. We used plPythonU to copy the file
from the filesystem to a temporary table. Then from the temporary table
insert into the permanent table.

Sincerely,

Joshua D. Drake


>
> so the data needs to be parsed and if the row exists it needs to be
> updated, otherwise new record is going to be created. My problem is
> with parsing:
>
> 1-what would be a good approach to do it? is plpgSQL suitable for
> this? or should I use PHP or other language to to that and run as a
> cron job/service?
>
> 2-how about committing transactions?  would there be a problem if it
> is done at the end of all the updates?
>
> Thank you
> Paul
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
--
Command Prompt, Inc., Your PostgreSQL solutions company. 503-667-4564
Custom programming, 24x7 support, managed services, and hosting
Open Source Authors: plPHP, pgManage, Co-Authors: plPerlNG
Reliable replication, Mammoth Replicator - http://www.commandprompt.com/