CODEWITHSUNDEEP
ruby-on-rails-3csvpostgresql-9.1
Ryan Clark
Ryan Clark

Reputation: 764

Rails and Postgres - COPY from .cvs to table while skipping created_at column

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

Upvotes: 4

Views: 2028

Answers (2)

qnm
qnm

Reputation: 521

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

Upvotes: 0

dpassage
dpassage

Reputation: 5453

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.

Upvotes: 3

Related Questions