update table from csv file

[Kirk Wythers]

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

If the structure of the table is

id data1 data2 data3

and the structure of the CSV file is

id data1 data2 data3

and I need to update all the rows in data3 where the id = id.

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;

Or am I completely off course?

[Craig Ringer]

Post by Kirk Wythers
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

Post by Kirk Wythers
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
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