Re: Fast Insert and Update (through COPY)

On Wed, Dec 15, 2010 at 10:17 AM, Yan Cheng CHEOK <yccheok(at)yahoo(dot)com> wrote:
> Previously, I am inserting a bulk of data by using the following way.
>
> for each item
>    update item into table
>    if row count is 0
>        insert items into table
>
> I realize I am suffering performance problem.
>
> Later, I realize it is much fast by using
>
> COPY
>
> I am very happy with the speed.
>
> Later, I realize that COPY doesn't work well, if I already have a row with same unique key. What I did is
>
> # Try to remove old rows first
> delete row where <condition>
> # And perform really fast insertion
> COPY
>
> I was wondering, is this a common technique being use for fast bulk data insertion? Is there other techniques.

Copy your data to a scratch table:
create temp table scratch (like foo);
copy scratch...
then do the update:
delete from foo where exists (select * from scratch where something);
then
insert into foo select * from scratch;

merlin