From: Casey T. Deccio <ctdecci@sandia.gov>
To: pgsql-sql@postgresql.org
Subject: Building a database from a flat file
Date: Wed, 02 Mar 2005 16:15:21 -0800
Message-ID: <1109808921.12430.75.camel@boomerang.ran.sandia.gov> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ctdecci@sandia.gov
Subject: Re: Building a database from a flat file
In-Reply-To: <1109808921.12430.75.camel@boomerang.ran.sandia.gov>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox