Re: Importing/Appending to Existing Table

You also don't have to import the source files directly into the live table.
Instead you could create a "staging" table that has no constraints where you
can import everything, do some review and updates, then merge that table
over to the live one. Depending on how many files you are dealing with and
whether you want or need to pre-combine them a more forgiving staging table
may come in quite handy. Especially consider the fact that you likely will
not know the maximum length used for various "text" fields you can make the
staging table fields varchar(255) (or even text) and then do some length
checks before importing the data into the accurately sized live table.

Dave

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Rich Shepard
Sent: Wednesday, February 02, 2011 9:30 AM
To: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Importing/Appending to Existing Table

On Wed, 2 Feb 2011, Greg Williamson wrote:

> It will not overwrite any existing data; if the table has constraints
> that will prevent duplicates then the entire load will fail if any
> item fails (it is a single transaction).
>
> If the table doesn't have such constraints then duplicate data in the
> copy file will result in duplicate rows.

Greg,

These data originate in highly undisciplined Excel spreadsheets over the
past 10+ years. They need a lot of preprossing in emacs and python scripts
I've written but there are no primary keys or other constraints until all
the data are entered then moved to the actual application tables. I will not
be surprised to discover duplicate data because of data-entry errors, but
there should not be any duplicates in these permit compliance monitoring
results.

Thanks,

Rich

--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general