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: (view raw, whole thread or download thread mbox)
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;
> 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...

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;

> 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! Team #1828

In response to


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-2017 The PostgreSQL Global Development Group