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
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 |