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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
