From: Ahmad Fajar <ahmadfajar@i2.co.id>
To: 'Michael Artz' <mlartz@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Bulk loading/merging
Date: Sun, 4 Jun 2006 19:00:32 +0700
Message-ID: <003601c687ce$7aee7b20$7f00a8c0@kicommunication.com> (raw)
In-Reply-To: <e9c163070606011104w1d0cf7a6jca8cf5b36f2d8de3@mail.gmail.com>
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,
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ahmadfajar@i2.co.id, mlartz@gmail.com
Subject: Re: Bulk loading/merging
In-Reply-To: <003601c687ce$7aee7b20$7f00a8c0@kicommunication.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox