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 

Answer 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

Answer 2

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;