Обсуждение: update from a csv file?

I have been using COPY FROM to do a mass import of records from CSV files into a new database. I have discover however,
asmall 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
violatesthe 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?

Thanks

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

Am Fri, 28 Dec 2012 00:47:35 -0500
schrieb François Beausoleil <francois@teksol.info>:

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

> 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;

The "INCLUDING ALL" will also generate the same unique keys that let
the COPY fail in the original table so omit that.

bye,

-christian-