Import csv file and update existing table in Postgres

Question

I have a Postgres database on Heroku. I connect to it using pgadmin. I exported a table data to csv. Edited some of the cells and now I want to replace the same table with the corrected csv. But when I try to import the csv I get this error

Error while importing updated csv on pgadmin

I researched on this error. I understood that it tries to add more rows to existing table and the primary key clashes. But I couldn't get my solution. I want to replace the table with new updated csv.

Thanks

Import the CSV into a separate table then use SQL to update/insert the rows into the real table — user330315
– user330315, Commented Jun 18, 2015 at 8:24
@VaoTsun Will it affect DB/Table schema if i do as you suggest? — Grahesh Parkar
– Grahesh Parkar, Commented Jun 18, 2015 at 8:26
yes, it will. you wrote you want to REPLACE data in table using data from csv. so you can IN TRANSACTION truncate table and copy from file, see if you like it and either COMMIT or ROLLBACK — Vao Tsun
– Vao Tsun, Commented Jun 18, 2015 at 8:34

Vao Tsun · Accepted Answer · 2015-06-18 08:58:56Z

2

SQL>begin;
BEGIN
Time: 0.366 ms
SQL>truncate table t;
TRUNCATE TABLE
Time: 3.068 ms
SQL>select * from t;
 t
---
(0 rows)

Time: 2.844 ms
SQL>copy t from '/tmp/t';
COPY 2
Time: 1.693 ms
SQL>select * from t;
               t
-------------------------------
 2014-10-09 08:09:58.241592+00
 2015-06-17 09:18:05.731139+00
(2 rows)

Time: 1.823 ms
SQL>end;
COMMIT
Time: 78.102 ms

edited Jun 18, 2015 at 8:58

answered Jun 18, 2015 at 8:32

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Import csv file and update existing table in Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related