Copy multiple CSV files into postgres with incrementing id

Question

I have multiple CSV files and I am getting new ones every day. The Data from each file should be added to the postgres DB regularly. The Problem is that every CSV file has a column with id. So when adding the second file

COPY public."ERROR" FROM 'C:\CSV\error.csv' DELIMITER ',' CSV HEADER;

I already get the error:

ERROR:  duplicate key value violates unique constraint "ERROR_pkey"
DETAIL:  Key (id)=(0) already exists.
KONTEXT:  COPY ERROR, line 2

However, in the DB I still need an id column as primary-key so I cannot just drop it. Is there a way to have an incrementing id automatically generated?

For example, like this.

CSV File 1:                        CSV File 2:
id  error           value          id   error           value
0   engine          10             0    engine          22
1   lamp_dashboard  15             1    door_left       13
2   door_left       17             2    indicator_left  37
3   boot            8              3    indicator_right 65
4   front_wheel     13             4    cockpit         16

Data in DB:
id  error           value
0   engine          10
1   lamp_dashboard  15 
2   door_left       17
3   boot            8
4   front_wheel     13
5   engine          22
6   door_left       13
7   indicator_left  37
8   indicator_right 65
9   cockpit         16

Unrelated to your problem, but: you should really avoid those dreaded quoted identifiers. They are much more trouble than they are worth it. wiki.postgresql.org/wiki/… — user330315
– user330315, Commented Oct 19, 2020 at 11:22

ggeorgiana · Accepted Answer · 2020-10-19 11:38:28Z

1

I think it can be solved by specifying the CSV columns you want to be copied (without the ID). In this way should automatically increment the ID. https://stackoverflow.com/a/29963692/8021888

answered Oct 19, 2020 at 11:38

ggeorgiana

262 bronze badges

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

Comments

user330315 · Accepted Answer · 2020-10-19 11:22:14Z

1

I think your only chance is to copy the files into a staging table, the copy the data from there to the real table:

create temporary table import_data
(
  id integer, 
  error text, 
  value integer
);

COPY import_data FROM 'C:\CSV\error.csv' DELIMITER ',' CSV HEADER;

insert into public."ERROR"  (error, value)
select error, value
from import_data;

answered Oct 19, 2020 at 11:22

user330315

2 Comments

Kuempsmichi Over a year ago

I tried it in different versions but I am always getting

ERROR:  null value in column "id" of relation "ERROR" violates not-null constraint DETAIL:  Failing row contains (null,......

user330315 Over a year ago

Then your id column isn't defined as identity (or serial) which means the value isn't generated automatically. In that case you will need to change to primary key to something different, remove it or make that column an identity (or serial) column

Collectives™ on Stack Overflow

Copy multiple CSV files into postgres with incrementing id

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related