rails postgres insert

Question

I'm trying to import data from a csv file following the example given by Jonaphin at http://www.genesx.com/2012/06/import-csv-files-100x-faster-in-rails-3/

I'm trying to get the index of the last insert from postgres, but for some reason the code isn't working.

def process_data!(file, survey_id)
 headers = [
    "zip_code",
    "booking_date"
 ]
 ActiveRecord::Base.establish_connection

 created_at = Time.now.strftime("%Y-%m-%d %H:%M:%S")

 CSV.foreach(file, {headers: :first_row}) do |row|
  sql_keys = []
  sql_vals = []

  headers.each_with_index do |key, idx|
    val = row[idx]

    sql_keys << key
    sql_vals << ActiveRecord::Base.connection.quote(val)
  end

  sql = "
    INSERT INTO bookings (survey_id, #{sql_keys.join(', ')}, created_at, updated_at)
    VALUES (#{survey_id}, #{sql_vals.join(', ')}, '#{created_at}', '#{created_at}')
  "

  booking_id = ActiveRecord::Base.connection.insert(sql)

 end
end

booking_id should now have the id of the last inserted row, but for some reason it is nil.

I tried running ActiveRecord::Base.connection.insert(sql) in the console and it worked (i.e., it returned the id I wanted).

Anyone know what's going wrong here? Is there some setting I need to change to allow postgres to return the last id?

Clodoaldo Neto · Accepted Answer · 2012-10-17 19:26:47Z

1

INSERT INTO bookings (survey_id, #{sql_keys.join(', ')}, created_at, updated_at)
VALUES (#{survey_id}, #{sql_vals.join(', ')}, '#{created_at}', '#{created_at}')
returning survey_id

answered Oct 17, 2012 at 19:26

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

2 Comments

cit Over a year ago

This works great -- thanks! (Should be "returning id", though)

bayendor Over a year ago

I have been using a similar approach, but in Rails 4.1 ActiveRecord::Base.connection is deprecated and this throws an error when using RETURNING id in the SQL statement.

Collectives™ on Stack Overflow

rails postgres insert

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related