COPY equivalent for updates

From: Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it>
To: pgsql-sql(at)postgresql(dot)org
Subject: COPY equivalent for updates
Date: 2008-07-15 06:10:25
Message-ID: 20080715081025.2cb5e3e5@dawn.webthatworks.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Is there a COPY equivalent for updates?

eg I've

create table t1 (
id int primary key,
col1 int,
col2 int,
col3 varchar(32)
);

and a CSV file
10,4,5,"abc"
13,7,3,"def"
18,12,77,"ghi"

I'd like to

UPDATE t1 (col1, col2, col3) from file with @1 as primary key;
or
UPDATE t1 (col1, col2, col3) from file where @1=id;

sort of...

Otherwise what is the fastest approach?

I can think of 2 approaches:
1)load a temp table with COPY

update t1 set col1=temp_t1.col1, col2=temp_t1.col2
where t1.id=temp_t1.id;

2) use awk to generate update statements.

Supposing I could neglect the awk execution time, will COPY + UPDATE
be faster than executing a list of UPDATE?

Considering I've to deal with a where clauses anyway... when (and
if) should I create an index on the id of temp_t1?
t1 will contain 700-1M records while I may update a maximum of 20K a
time.

thanks

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Simon Riggs 2008-07-15 06:17:43 Re: Rollback in Postgres
Previous Message Anoop G 2008-07-15 04:17:25 Re: how to perform minus (-) operation in a dynamic query