CODEWITHSUNDEEP
postgresqlcsvpostgresql-9.2
Andrius
Andrius

Reputation: 21198

PostgreSQL - CSV file import error

I'm trying to import csv file into PostgreSQL table (9.2). I get error that one column value has wrong input syntax, but it seems PostgreSQL mismatches columns and its values.

I'm importing CSV with this command:

\copy project_task from '/home/user/downloads/project_task_export.csv' WITH DELIMITER ',' CSV HEADER

Error looks like this:

ERROR:  invalid input syntax for integer: "4.25"
CONTEXT:  COPY project_task, line 3, column sequence: "4.25"

My csv data looks like this:

id,create_uid,create_date,write_date,write_uid,remaining_hours,date_start,description,sequence,date_end,active,planned_hours,partner_id,delay_hours,user_id,name,date_deadline,notes,type_id,company_id,priority,total_hours,state,progress,user_group_id,project_id,effective_hours,context_id,timebox_id
185,8,2012-06-05 09:51:09.562849,2012-09-10 08:52:05.789792,26,4.25,2012-06-19 15:54:07,"asdcxzzsrt ab cdefgqwer tyuipok asmmdmksa kmsaaqqwerr.
",10,,t,12,,0.0,6,byrteqwas oeirklm askeiurjqqpl,,,,1,2,12.0,open,64.579999999999998,,9,7.75,1,4

If you looked closely in this CSV data and error, column sequence is 9th column, and error showed looks like PostgreSQL uses 6th columns value for nine column. Why it is doing it? Or maybe I'm doing something wrong?

Upvotes: 4

Views: 10170

Answers (1)

Daniel Vérité
Daniel Vérité

Reputation: 61726

Presumably the problem is that the order of the columns in the CSV file does not match the order in the destination table. You'd think that COPY would map the columns according to the header in the first line, but it actually ignores the first line, as said in this doc excerpt:

HEADER
Specifies that the file contains a header line with the names of each column in the file. On output, the first line contains the column names from the table, and on input, the first line is ignored. This option is allowed only when using CSV format.

You need to add to \copy the list of columns in parentheses after the table's name, ordered as in the file:

\copy project_task(id,create_uid,.....,context_id,timebox_id) from '/path/to/file'...

Upvotes: 7

Related Questions