Re: looking for an easier way to update

From: Andrew McMillan <andrew(at)catalyst(dot)net(dot)nz>
To: "KUCHARSKI, DAVID R(dot)" <dave(at)iemco(dot)com>
Cc: pgsql <pgsql-novice(at)postgresql(dot)org>
Subject: Re: looking for an easier way to update
Date: 2001-11-06 01:54:47
Message-ID: 1005011687.15100.263.camel@kant.mcmillan.net.nz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message John Burski 2001-11-06 14:59:40 Re: Casting problem
Previous Message Tom Lane 2001-11-05 23:05:53 Re: Casting problem