PostgreSQL - CSV file import error

Question

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?

You have some unquoted strings too. Is that just a typo when creating the question, or in the actual data? — Tim
– Tim, Commented Mar 7, 2013 at 13:45
If Daniel's answer below is not helpful, please post the results of \d project_task as this will show the order of the columns. I would also suggest getting rid of the DELIMITER ',' since you are also specifying CSV (and thus it is the default) — Chris Travers
– Chris Travers, Commented Mar 7, 2013 at 14:30
@Tim This CSV file was created using another PostgreSQL database, don't know why it made one string with quotes, the other without. — Andrius
– Andrius, Commented Mar 8, 2013 at 6:42

Daniel Vérité · Accepted Answer · 2013-03-07 13:32:07Z

7

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'...

answered Mar 7, 2013 at 13:32

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL - CSV file import error

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