0

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
Kuempsmichi
  • 69
  • 2
  • 10
  • 2
    Unrelated to your problem, but: you should really avoid those dreaded quoted identifiers. They are much more trouble than they are worth it. https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_upper_case_table_or_column_names –  Oct 19 '20 at 11:22

2 Answers2

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;
  • 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,...... ```` – Kuempsmichi Oct 19 '20 at 14:59
  • 1
    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 –  Oct 19 '20 at 15:14
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

ggeorgiana
  • 26
  • 2