class ActiveRecord::Base
Public Class Methods
bulk_write(fields, rows, upsert = nil)
click to toggle source
Insert or update a batch of rows. When upsert is nil, insert only. Otherwise insert or update on conflict.
Parameters: fields: an array of field names to insert value rows: an array of array of values to insert. The element, values array,
must correspond with the fields parameter.
upsert:
A hash with the following keys:: conflict: an array of field names on which a unique constrain may fail the insert update: an array of field names to update when conflict happens. If omitted, it will be "fields - upsert[:conflict]". where: where clause to determine if a row will be updated when conflict happens. "EXCLUDED" is used for referencing the row that failed in insert. If omitted, update all rows that have conflict.
Return: The number of affected rows
# File lib/activerecord/bulkwrite.rb, line 25 def self.bulk_write(fields, rows, upsert = nil) return 0 if rows.empty? columns = fields.map {|name| column_for_attribute(name) } rows = rows.map do |row| values = row.map.with_index do |val, i| # NOTE: Here quote method treats val as a Ruby value from # Value#type_cast_from_user, and thus won't convert a String time to # Time object before passing the string time to database. # That's OK for a time string in UTC, but NOT FOR LOCAL TIME, since # ActiveRecord saves time as type "DATETIME WITHOUT TIMEZONE" in database! connection.quote(val, columns[i]) end.join ', ' "(#{ values })" end field_list = fields.map{|e| %Q("#{e}") }.join ', ' sql = "INSERT INTO #{ table_name } (#{ field_list }) VALUES #{ rows.join ', ' }" if upsert if !upsert[:update] update = fields.map(&:to_s) - upsert[:conflict].map(&:to_s) else update = upsert[:update] end update = update.map {|field| "#{ field } = EXCLUDED.#{ field }" } sql += " ON CONFLICT (#{ upsert[:conflict].join ', ' }) DO UPDATE SET #{ update.join ', ' }" if upsert[:where] sql += " WHERE #{ upsert[:where] }" end end # res is a PG::Result object. See # https://deveiate.org/code/pg/PG/Result.html # for its details. res = connection.execute(sql) res.cmd_tuples end