Re: update table from csv file

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
thedatabase, but on rows that have a primary key and have already been inserted (so I can't use COPY FROM because it
violatesthe 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
stuckhowever. 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