PostgreSQL ERROR: invalid input syntax for integer

Question

This is my first time copying a CSV file to my Rails 4 Heroku app.
Following the steps below receives an error message.

Message received from command line:

ERROR:  invalid input syntax for integer: "Employee personnel files"
CONTEXT:  COPY articles, line 29, column id: "Employee personnel files"

Using this command:

PGPASSWORD=PWHERE psql -h HOSTHERE -U USERHERE DBNAMEHERE -c "\copy articles FROM 'lib/articles' WITH CSV HEADER;"

Here's a snippet of the CSV file:

 line 1 "Title","Body"
 line 2 "Employee personnel files","
    As an HR professional you are no stranger to paperwork. It seems that for every employment action - applying, interviewing, hiring, disciplining, on and on - there is a specific form that needs to be filled out. Making sure you complete the paperwork properly is only half the battle though. Once you finish completing a form, you are faced with a whole new issue: what to do with it.  Being the smarty that you are, you know that proper documentation is key in protecting your company in the unfortunate case of a lawsuit, but knowing what needs to be kept where and for how long and who can see it can be kind of tricky. Let's take a minute to go over the basics.

...

 line 29 Looking for more sample polices and important forms? Click here to gain access."

Any suggestions on what is missing?

that's not csv. csv has one record per-line. you have one record spread across MULTIPLE lines. — Marc B
– Marc B, Commented Feb 25, 2016 at 19:14
csv is a last resort when everything else fails format, and even then you should think twice about using it. use pretty much anything else, e.g. xml - which has explicit provisions for handling internal escaping of data which would otherwise make for an invalid xml document. — Marc B
– Marc B, Commented Feb 25, 2016 at 19:17
As Marc said, it should be one record per line. You must also remember to create a table without an ID column if you plan on using the COPY command or else you'll have to put a unique sequential number value in each row to fit into the ID column on copying. — bkunzi01
– bkunzi01, Commented Feb 25, 2016 at 19:18
@MarcB how would one import an xml file? I started with CSV on my local machine, which worked very well, using /lib/tasks/import.rake file to import the articles data. I'm assuming this isn't the way things work with rails production sites? — jgrant
– jgrant, Commented Feb 25, 2016 at 19:39

blankenshipz · Accepted Answer · 2016-02-25 19:20:12Z

0

It seems like you might have a field defined as an integer in the table that postgres is trying to store the string value "Employee personnel files". Check your table for additional fields that aren't defined in your CSV.

Header is used to specify that the CSV has a header row so that it can be ignored during the load process, not as a specification of what fields to import the data into.

If you're developing a rails application and using migrations you probably have an id field that's defined as an integer.

answered Feb 25, 2016 at 19:20

blankenshipz

3851 gold badge2 silver badges12 bronze badges

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

Comments

bkunzi01 · Accepted Answer · 2016-02-25 19:20:34Z

0

Firstly, make sure you are creating your table, that is going to house this information, that doesn't have an ID column. The ID column is automagically created by rails and will be a problem unless you plan on adding numbers to every row. You can then add an ID column after the fact to make it right.

Ex. create_table :products, id: false do |t|

This will leave off an ID column. Then, once you get all records formatted correctly in which there is one record per line, you will be ready to COPY them into postgres using the /COPY command. You can then add an ID column back in after the fact.

answered Feb 25, 2016 at 19:20

bkunzi01

4,5811 gold badge20 silver badges27 bronze badges

8 Comments

jgrant Over a year ago

Where would I create create_table :products, id: false do |t|? I commented above regarding the method I used for my development database

bkunzi01 Over a year ago

When using the copy command for postgres, you need to already have a table in the database made. What is the name of the table you want to put this information in? Give me all of the columns you need and their datatypes such as string or integer etc. and I'll type it out for you.

jgrant Over a year ago

The table is named Article

bkunzi01 Over a year ago

Ok: On your command line inside your rails app do > rails g model Article title:string body:string Let me know when that is done

jgrant Over a year ago

I have the Article model. I did a scaffold with Article before trying to import csv to heroku

|

jgrant · Accepted Answer · 2016-02-26 01:32:34Z

0

I was able to seed my heroku database with the heroku run rake db:seed command and this import.rake file in /lib/tasks.

    require 'csv'

    namespace :import do

      desc 'An optional description for what the task does'
      task :articles => :environment do
            CSV.foreach("lib/articles.csv", headers: true, encoding: "UTF-8") do |row|
            Article.create!(
              title: row["Title"], 
              body: row["Body"], 
            )
          end
      end
    end

edited Feb 26, 2016 at 1:32

answered Feb 26, 2016 at 0:44

jgrant

5926 silver badges20 bronze badges

Collectives™ on Stack Overflow

PostgreSQL ERROR: invalid input syntax for integer

3 Answers 3

Comments

8 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related