Fast Insert and Update (through COPY)

On Wed, Dec 15, 2010 at 10:17 AM, Yan Cheng CHEOK <yccheok@yahoo.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