4

My client has given me a list of vehicles for a project. I need to get them into a table I can use, but they're currently in a .cvs file. I've read around and found some info, but nothing that solves my particular problem.

I've generated a model that matches the info in the .cvs file(id, year, make, model, trim), run the migration, and now have the table I need. The issue comes up when I try to use psql COPY. Here's what I've read will work:

copy list_vehicles from '/path/to/list.csv' DELIMITERS ',' CSV;

but it gives me

ERROR:  missing data for column "created_at"

Fine, so I try this:

copy list_vehicles (id, year, make, model, trim)  from '/path/to/list.csv' DELIMITERS ',' CSV;

and I get back 

ERROR:  null value in column "created_at" violates not-null constraint

Ok, so then this should work:

copy list_vehicles (id, year, make, model, trim)  from '/path/to/list.csv' DELIMITERS ',' WITH NULL AS ' ' CSV FORCE NOT NULL created_at;

nope, 

ERROR:  FORCE NOT NULL column "created_at" not referenced by COPY

I'm not sure where to go from here. I was thinking of trying to take the created_at column back out for now, then add it in another migration? Any guidance would be much appreciated.

Thanks

Community
  • 1
  • 1
Ryan Clark
  • 764
  • 1
  • 8
  • 29

2 Answers2

3

The created_at column is made automatically by Rails most of the time when you run a migration to create a table for a new model. It's generally populated most of the time by the Rails default code when you create a new model object in your application.

You're loading the data directly into the database, though, bypassing all the Rails code. Which is fine, but you also need to do the things Rails does.

I think the easiest way is going to be to remove the created_at and other columns from the database directly, loading your CSV file, and then adding the columns back in.

dpassage
  • 5,423
  • 3
  • 25
  • 53
  • Thanks, I was kind of leaning this way. I know rails does this and I can manually remove the column. I don't think I would need to remove updated_at as it can be blank, right? What would be the best way to remove the column? With a migration? Not sure about best practice here... – Ryan Clark Oct 23 '12 at 00:49
  • If this data load is a one-time thing, you may want to do it all in SQL. Remove the column, load your data, add the column again. If you think you'll need to do it occasionally, think about adding some code in the Rails app to do it for you. – dpassage Oct 23 '12 at 01:33
  • As of now, it looks like a quarterly update. I'd like to think we will have a better solution down the road, but for the time being, I don't mind rebuilding the table each time. Now, if only I knew the best way to go about doing so... – Ryan Clark Oct 23 '12 at 03:48
  • Probably directly at the command line with the SQL commands. See http://www.postgresql.org/docs/9.1/interactive/ddl-alter.html. – dpassage Oct 23 '12 at 15:32
0

You can also have Postgres read from STDIN, allowing you to modify the data prior to loading it.

I use something like this, which is untested, but should give you an outline.

connection = ActiveRecord::Base.connection.raw_connection

connection.exec("COPY #{tablename} (#{fields},created_at,updated_at) FROM STDIN")

data = File.open(datafile)
data::gets # abandon the header line (if needed)
data.each_with_index do |line, index|
  connection.put_copy_data(line + ",,")
end

connection.put_copy_end

res = connection.get_result
if res.result_error_message
  puts "Result of COPY is: %s" % [ res.result_error_message ]
end
qnm
  • 521
  • 3
  • 14