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.

Thanks and Regards
Yan Cheng CHEOK

Search Discussions

  • Merlin Moncure at Dec 15, 2010 at 5:25 pm

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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedDec 15, '10 at 3:17p
activeDec 15, '10 at 5:25p
posts2
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Yan Cheng CHEOK: 1 post Merlin Moncure: 1 post

People

Translate

site design / logo © 2022 Grokbase