Re: [WW Spam: medium] Building a database from a flat file

Could you set up functions triggers on the temp table that would do updates
and inserts on the other tables from the data entered into the temp table?

Then all you would have to do is inter the data into the temp table

-----Original Message-----
From: Casey T. Deccio [mailto:ctdecci(at)sandia(dot)gov]
Sent: Wednesday, March 02, 2005 7:15 PM
To: pgsql-sql(at)postgresql(dot)org
Subject: [WW Spam: medium] [SQL] Building a database from a flat file

A database I am currently using is built and updated periodically from a
flat csv file (The situation is rather unfortunate, but that's all I
have right now). The schema I use is more complex than the flat file,
so I follow a process to populate the tables with the data from the
file. First I slurp the whole file into one temporary table, whose
columns correspond to the columns in the file. Then I DELETE all the
existing rows from the tables in the schema and perform a series of
queries on that table to INSERT and UPDATE rows in the tables that are
in the schema. Then I DELETE the data from the temporary table. I do
it this way, rather than trying to synchronize it, because of the
inconsistencies and redundancies in the flat file.

There is more than one problem with this, but the largest is that I
would like to perform this whole database rebuild within one
transaction, so other processes that need to access the database can do
so without noticing the disturbance. However, performing this set of
events (besides populating the temporary table) within a single
transaction takes a long time--over an hour in some cases.

What are some suggestions to help improve performance with replacing one
set of data in a schema with another?

Casey

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
message can get through to the mailing list cleanly