up vote 2 down vote favorite

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?

share|improve this question
You have some unquoted strings too. Is that just a typo when creating the question, or in the actual data? – Tim Mar 7 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 Mar 7 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. – oerp Mar 8 at 6:42

1 Answer

up vote 1 down vote

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'...
share|improve this answer

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.