Search Postgresql Archives

Re: update from a csv file?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux