Re: looking for an easier way to update

On Tue, 2001-11-06 at 11:15, KUCHARSKI, DAVID R. wrote:
> I'm very new to all of this so I'm looking for answers in the SIMPLEST
> of terms.  I have a DB that is a multimillion item catalog.  Every month
> I get an update to the catalog.  The original catalog is maintained by
> someone else running an Oracle system on Windows NT or 2000 servers.
> they output a text file and burn it to cd and mail me the updates as a
> zip file.  the tables are converted to .txt windows files as tab
> delimited text.  Line one is the header and the rest of the file is the
> contents of the table.  Currently I have to go in and strip the headers
> before I can use the data.  Then I have to delete the current contents
> of the table and use COPY from to bring in the new information.  What
> I'm looking for is a way to get postgreSQL to update the tables from the
> information in the text files and only overwrite what has changed while
> leaving thue unchanged stuff intact.  Is my thinking wrong?
> Can it do that?
> if so, HOW? remember I'm very new  to this so please be as explicit as
> possible.

One simplification might be to do the COPY into a new (possibly
temporary) table, then you could do a DELETE of rows from your target
which have changed (presumably you can identify which ones somehow -
hopefully there is a date stamp on the incoming records.  That done, you
can do a INSERT ... SELECT to get all the changed records from your
temporary-ish table into your target table.

Maybe that sounds complicated, but I think it would be easier to script
than your existing process.

Also, remember that if you're in a GNU textutils environment, tail -n+2
will strip the first line from a file.

Hope this helps,
                        Andrew.
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/        PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201    MOB: +64(21)635-694    OFFICE: +64(4)499-2267