I have a CSV file whose data is to be imported to Postgres database , I did it using import function in pgadmin III but the problem is my CSV file changes frequently so how to import the data overwriting the already existing data in database from CSV file ?
3
-
Does the database data only reflect the CSV data? That is, if you wipe the database table containing the CSV data and reimport it, will you lose any information?Feneric– Feneric2018-09-10 10:52:51 +00:00Commented Sep 10, 2018 at 10:52
-
Yes, database data reflects only CSV data, if I re-import it , only the updated CSV data should be presentBKK– BKK2018-09-10 11:02:46 +00:00Commented Sep 10, 2018 at 11:02
-
2So just truncate the table and do the copy again?404– 4042018-09-10 12:47:33 +00:00Commented Sep 10, 2018 at 12:47
Add a comment
|
1 Answer
You can save WAL logging through an optimization between TRUNCATE/COPY in the same transaction. The basic idea is to wipe the database table with TRUNCATE and reimport the data with COPY. This doesn't need to be done manually with pgAdmin each time. It can be scripted with something like:
BEGIN;
-- The CSV file is 'mydata.csv' and the table is 'mydata'.
TRUNCATE mydata;
COPY mydata FROM 'mydata.csv' WITH (FORMAT csv);
COMMIT;
Note that it requires superuser access to work. The COPY command also takes various arguments, so you can adjust for different settings for null and headers etc.
Finally it should be noted that you ideally want these both to be in the same transaction. I'm not going to over-complicate this example here though as this level of care isn't needed in many of the real-world sorts of cases where one is copying in a CSV file. If you think your situation needs it, it's not too hard to track down.
