CODEWITHSUNDEEP
postgresql
BKK
BKK

Reputation: 503

How to update the Postgresql using CSV file multiple times

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 ?

Upvotes: 0

Views: 253

Answers (1)

Feneric
Feneric

Reputation: 861

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.

Upvotes: 1

Related Questions