X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 6D1C39FA43C for ; Sun, 4 Jun 2006 09:13:22 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 07744-03 for ; Sun, 4 Jun 2006 09:13:09 -0300 (ADT) X-Greylist: delayed 00:12:28.352778 by SQLgrey- Received: from ki-communication.com (unknown [202.137.6.59]) by postgresql.org (Postfix) with ESMTP id CBEE79F9DB0 for ; Sun, 4 Jun 2006 09:13:05 -0300 (ADT) Received: from edp01wks ([192.168.0.127]) by ki-communication.com with Microsoft SMTPSVC(6.0.3790.1830); Sun, 4 Jun 2006 19:00:32 +0700 From: "Ahmad Fajar" To: "'Michael Artz'" Cc: Subject: Re: Bulk loading/merging Date: Sun, 4 Jun 2006 19:00:32 +0700 Keywords: Postgresql Message-ID: <003601c687ce$7aee7b20$7f00a8c0@kicommunication.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0037_01C68809.274D5320" X-Mailer: Microsoft Office Outlook 11 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869 Thread-Index: AcaFrZrTcvtL9urDRn2k+1Vc8zXc7wCH2sdg In-reply-to: X-OriginalArrivalTime: 04 Jun 2006 12:00:32.0795 (UTC) FILETIME=[7B47BEB0:01C687CE] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/31 X-Sequence-Number: 19388 This is a multi-part message in MIME format. ------=_NextPart_000_0037_01C68809.274D5320 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit On 06/02/2006, Michael Artz wrote: hmm, I don't quite understand what you are saying and I think my basic misunderstanding is how to use the UPDATE ... WHERE EXISTS to merge data in bulk. Assuming that I bulk COPYed the data into a temporary table, I'd need to issue an UPDATE for each row in the newly created table, right? For example, for a slightly different key,count schema: CREATE TABLE kc (key integer, count integer); and wanting to merge the following data by just updating the count for a given key to the equivalent of OLD.count + NEW.count: 1,10 2,15 3,45 1,30 How would I go about using UPDATE ... WHERE EXISTS to update the "master" kc table from a (temporary) table loaded with the above data? May be, this method could help you: CREATE TEMP TABLE clip_temp ( cids int8 NOT NULL, clip_id int8 NOT NULL, mentions int4 DEFAULT 0, CONSTRAINT pk_clip_temp PRIMARY KEY (cids, clip_id)) ) insert data into this temporary table... then do: UPDATE clip_category SET mentions=clip_temp.mentions FROM clip_temp WHERE clip_category.cids=clip_temp.cids AND clip_category.clip_id=clip_temp.clip_id DELETE FROM clip_temp USING clip_category WHERE clip_temp.cids=clip_category.cids AND clip_temp.clip_id=clip_category.clip_id INSERT INTO clip_category (cids, clip_id, mentions) SELECT * FROM clip_temp DROP TABLE clip_temp; Best regards, ahmad fajar, ------=_NextPart_000_0037_01C68809.274D5320 Content-Type: text/html; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable

On 06/02/2006, Michael Artz wrote: =

hmm, I don't quite = understand what you are saying and I think my basic misunderstanding is how to use = the UPDATE ... WHERE EXISTS to merge data in bulk.  Assuming that = I bulk COPYed the data into a temporary table, I'd need to issue an UPDATE for each row in the newly created table, right?  =

 

For example, for a slightly different key,count schema:

CREATE TABLE kc (key = integer, count integer);

and wanting to merge the = following data by just updating the count for a given key to the equivalent of = OLD.count + NEW.count:

1,10

2,15

3,45

1,30

How would I go about using = UPDATE ... WHERE EXISTS to update the "master" kc table from a = (temporary) table loaded with the above data?

 

May be, this method could help = you:

CREATE TEMP TABLE clip_temp = (

  cids int8 NOT = NULL,

  clip_id int8 NOT = NULL,

  mentions int4 DEFAULT = 0,

  CONSTRAINT pk_clip_temp = PRIMARY KEY (cids, clip_id))

)

insert data into this temporary = table...

then = do:

 

UPDATE clip_category SET mentions=3Dclip_temp.mentions

FROM = clip_temp

WHERE = clip_category.cids=3Dclip_temp.cids

AND clip_category.clip_id=3Dclip_temp.clip_id

 

DELETE FROM clip_temp USING = clip_category

WHERE = clip_temp.cids=3Dclip_category.cids

AND clip_temp.clip_id=3Dclip_category.clip_id

 

INSERT INTO clip_category (cids, = clip_id, mentions)

SELECT * FROM = clip_temp

 

DROP TABLE = clip_temp;

 

 

Best = regards,

= ahmad fajar,

 

 

 

 

------=_NextPart_000_0037_01C68809.274D5320--