From: | Craig Ringer <craig(at)2ndQuadrant(dot)com> |
---|---|
To: | Kirk Wythers <kwythers(at)umn(dot)edu> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: update table from csv file |
Date: | 2012-12-28 05:18:37 |
Message-ID: | 50DD2BAD.5060709@2ndQuadrant.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 12/28/2012 12:31 AM, Kirk Wythers wrote:
> I have been using COPY FROM to do a mass import of records from CSV files into a new database. I have discover however, a small number of records ( a few thousand) in one of the files that contain new data that needs to be added to the database, but on rows that have a primary key and have already been inserted (so I can't use COPY FROM because it violates the primary key).
The standard solution is:
- COPY to a temporary table; then
- Use UPDATE ... FROM to merge the data from the temp table into the
main table, usually after locking the main table
> I have created a temporary table and used COPY FROM to load the update data into the temporary table. I seem to be stuck however. I thought I should be able to use the UPDATE command to update all columns and all rows the table.id = tmp_table.id
>
> Something like:
>
> UPDATE table FROM tmp_table WHERE table.id = tmp_table.id;
That's the usual solution, yes.
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
From | Date | Subject | |
---|---|---|---|
Next Message | Harry | 2012-12-28 05:28:50 | Re: Cursor fetch Problem. |
Previous Message | Alejandro Carrillo | 2012-12-27 21:17:29 | pg_dirtyread doesnt work |