current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 6.4 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 0 down vote favorite

I am importing a csv file into postgres, and would like to know how to import the correct data type while using the COPY command. For instance, I have a column column_1 integer; and want to insert the value 6 into it from my csv file.

I run the command copy "Table" from 'path/to/csv' DELIMITERS ',' CSV; and every time I try to do this I get the error ERROR: invalid input syntax for integer: "column_1". I figured out that it's because it is automatically importing every piece of data from the csv file as a string or text. If I change the column type to text then it works successfully, but this defeats the purpose of using a number as I need it for various calculations. Is there a way to conserve the data type when transferring? Is there something I need to change in the csv file? Or is there another datatype to assign to column_1? Hope this makes sense. Thanks in advance!

share|improve this question
1  
try like this COPY "Table" from 'path/to/csv' WITH CSV HEADER; – wingedpanther Nov 3 '14 at 6:48
    
Is there lot of column_1 string in your column or only one ? – Houari Nov 3 '14 at 9:11
    
I Have about 25 rows and 25 columns all populated with data that I already have in a spreadsheet and want to basically just copy and paste all the info rather than having to manualy re enter every cell. I've tried the header function as well and I had the same issue. – xTopShelfx Nov 3 '14 at 16:44
up vote 0 down vote accepted

I did this and it worked flawlessly: I put the plain number in the stack.csv
(The stack.csv has only one value 6)

# create table stack(i int);
#  \copy stack from 'stack.csv' with (format csv);

I read in your comment that you have 25 columns in your CSV file. You need to have at least 25 columns in your table. All columns need to be mapped from CSV. If you have more than 25 columns in table you need the map only the columns mapped from CSV. That's why it works at a text field because all data is put in one row cell.

If you have more columns that "fields" in your CSV file than the format is like this

\copy stack(column1, column2, ..., column25) from 'stack.csv' with (format csv);
share|improve this answer
    
What exactly are you doing here? Would you mind explaining why this is different – xTopShelfx Nov 4 '14 at 4:39
    
I read in your comment that you have 25 columns in your CSV file. You need to have at least 25 columns in your table. All columns need to be mapped from CSV. If you have more than 25 columns in table you need the map only the columns mapped from CSV. If you have , than which is default, so I ommited it. Other things different: I didn't put quotation around my table and the path to your CSV file is vague. Why did you ommit \ from the command? – Mladen Uzelac Nov 4 '14 at 7:15

Your Answer

 
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.