Re: update from a csv file?

Le 2012-12-27 à 09:54, Kirk Wythers a écrit :

> 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.
>
> Is this a job for the UPDATE command? or is there a better way to pull data from a CSV file in order to do a mass update?

You will want to COPY FROM on a new table that has the same structure:

BEGIN;
CREATE TEMPORARY TABLE original_table_name_temp( LIKE original_table_name INCLUDING ALL );
COPY original_table_name_temp FROM stdin;
-- If there are many thousands of rows
ANALYZE original_table_name_temp;
UPDATE original_table_name o
SET data3 = t.data3
FROM original_table_name_temp t
WHERE o.id = t.id;
COMMIT;

http://www.postgresql.org/docs/current/static/sql-update.html

You may also want to investigate the ON COMMIT option for CREATE TABLE: http://www.postgresql.org/docs/current/static/sql-createtable.html

Good luck!
François