Skip site navigation (1) Skip section navigation (2)

Re: COPY equivalent for updates

From: Decibel! <decibel(at)decibel(dot)org>
To: Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: COPY equivalent for updates
Date: 2008-07-16 03:13:42
Message-ID: 65E2EBCB-3E01-41A3-8928-23C78EF88AFF@decibel.org (view raw or flat)
Thread:
Lists: pgsql-sql
On Jul 15, 2008, at 1:10 AM, Ivan Sergio Borgonovo wrote:
> 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...

Sorry, there's nothing like COPY for UPDATE.

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

Almost certainly... databases like dealing with sets of data; what  
your proposing with AWK turns it into a ton of single-row statements.

Depending on what you're doing, it might well be fastest to...

BEGIN;
COPY temp_table FROM 'file';
DELETE FROM real_table WHERE real_table_id IN (SELECT real_table_id  
FROM temp_table);
INSERT INTO real_table SELECT * FROM temp_table;
COMMIT;

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

-- 
Decibel!, aka Jim C. Nasby, Database Architect  decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828


In response to

Responses

pgsql-sql by date

Next:From: A. KretschmerDate: 2008-07-16 05:37:48
Subject: Re: How to GROUP results BY month
Previous:From: Scott MarloweDate: 2008-07-16 01:55:28
Subject: Re: How to GROUP results BY month

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group