0

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!

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Luminusss
  • 571
  • 1
  • 6
  • 27
  • 1
    try like this `COPY "Table" from 'path/to/csv' WITH CSV HEADER;` – Vivek S. Nov 03 '14 at 06:48
  • Is there lot of `column_1` string in your column or only one ? – Houari Nov 03 '14 at 09: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. – Luminusss Nov 03 '14 at 16:44

1 Answers1

0

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);
Mladen Uzelac
  • 1,173
  • 1
  • 10
  • 14
  • What exactly are you doing here? Would you mind explaining why this is different – Luminusss Nov 04 '14 at 04: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 04 '14 at 07:15