Per the COPY documentation:
COPY moves data between PostgreSQL tables and standard file-system files. COPY TO copies the contents of a table to a file, while COPY FROM copies data from a file to a table (appending the data to whatever is in the table already).
...So it's not destructive. If you simply need to append the data in the CSV to a table, then COPY might be all you need:
http://www.postgresql.org/docs/8.3/static/sql-copy.html
For updates, one strategy might be to use a "staging" table which you truncate, COPY FROM the file to the staging table, and then use a stored procedure to update your main table from there. I haven't benchmarked this, but I would be shocked if this didn't yield superior performance to a solution which implements the update logic in the front-end.
Good luck,
Dave
Hi,
I'm fairly new to DBMS's and PostGre SQL in particular. Most of my experience is with MS Access. I've created MS Access to retrieve data from PostGre by linking tables and have done so without error. However, I am now in need of being able to update a PostGre table. The source will be a .csv file.
I've seen a "COPY" command in PostGre that will allow you to specify a .csv file as the source, but in looking at it it appears that it deletes and then creates the table with the stored data. I need to be able to Update or Append data from a file. Maybe going the route of a temp file then update from that.
I'm using Access as that is a point of comfort for me. I can write a .net procedure though I'm not exactly firm on the procedure.
Thoughts on how to do the entire front end in Access.
Or
Thoughts on whether to do it from a .net perspective.
MSAccess ( 2003 )
PostGreSQL ( 8.1.8 )
Regards,
Barry Pettis
CSO Atmel Corp
