Re: continuous copy/update one table to another

From: Terry <td3201(at)gmail(dot)com>
To: John R Pierce <pierce(at)hogranch(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: continuous copy/update one table to another
Date: 2010-03-01 00:40:09
Message-ID: 8ee061011002281640n59990d50q954af012c21468f0@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Sun, Feb 28, 2010 at 6:29 PM, John R Pierce <pierce(at)hogranch(dot)com> wrote:
> Szymon Guz wrote:
>>
>> Different doesn't mean that the id should be greater or lower, rather
>> should be different. I'd rather do something like:
>
> indeed, my code assumed that records were only INSERT'd into table1 and
> never UPDATE or DELETE'd.  my statement -did- have the advantage of being
> fast, at least assuming the id is an index on both tables.   if you do
> update records, you could use a seperate SERIAL/BIGSERIAL field for this,
> which you update on your INSERT's, and use this bigserial for your inserts,
> but you'd need a UPSERT kind of function to handle duplicate primary keys.
>
> checking for deletions will be more difficult and more importantly, more
> time consuming as it will likely require multiple full table scans of both
> tables.
>
>
>
> --
> 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
>

Good stuff. My rows won't change. I wrote a script to do all of the
logic in perl but clearly won't be as efficient as what you guys did
in 1 line. :)

In response to

Browse pgsql-general by date

  From Date Subject
Next Message C. Bensend 2010-03-01 01:05:37 Confusion about users and roles
Previous Message John R Pierce 2010-03-01 00:29:34 Re: continuous copy/update one table to another