Using UPSERT PostgreSQL from Rails 4.2

Question

How do I use "UPSERT" or "INSERT INTO likes (user_id,person_id) VALUES (32,64) ON CONFLICT (user_id,person_id) DO NOTHING" in PostgreSQL 9.5 on Rails 4.2?

I made a gem for Rails 5 if you have the opportunity to update: github.com/jesjos/active_record_upsert — Jesper
– Jesper, Commented Apr 4, 2016 at 13:39
@Rocco Because it's not Atomic and I prefer the database to handle my data as much as possible. — user3384741
– user3384741, Commented Apr 4, 2016 at 20:50
@user3384741 We use it with jruby where I work. Caveat is that you have to use a patched version of activerecord-jdbc: github.com/jensnockert/activerecord-jdbc-adapter/tree/… — Jesper
– Jesper, Commented Apr 5, 2016 at 4:39
find_or_create_by also doesn't handle race conditions. Throw in threading(unicorn) or multi systems, and find_or_create doesn't cut it. Perhaps it should be altered to use upsert. — baash05
– baash05, Commented Sep 3, 2016 at 13:05

pungoyal · Accepted Answer · 2016-11-07 19:21:38Z

1

Have a look at active_record_upsert gem here: https://github.com/jesjos/active_record_upsert. This does the upsert, but obviously only on Postgres 9.5+.

answered Nov 7, 2016 at 19:21

pungoyal

1,79815 silver badges17 bronze badges

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

1 Comment

DB140141 Over a year ago

This does not answer the question - he is looking for ON CONFLICT DO NOTHING. That gem only does ON CONFLICT DO UPDATE. Big difference!

Marcelo Xavier · Accepted Answer · 2018-11-29 09:40:54Z

You can achieve that by creating the SQL using the active records internals. You can use the arel_attributes_with_values_for_create to extract values for each active_record for the insert sql with the Rails attributes, something like:

INSERT_REGEX = /(INSERT INTO .* VALUES) (.*)/

def values_for(active_record)
  active_record.updated_at = Time.now if active_record.respond_to?(:updated_at)
  active_record.created_at ||= Time.now if active_record.respond_to?(:created_at)

  sql = active_record.class.arel_table.create_insert.tap do |insert_manager|
    insert_manager.insert(
      active_record.send(:arel_attributes_with_values_for_create, active_record.attribute_names.sort)
    )
  end.to_sql

  INSERT_REGEX.match(sql).captures[1]
end

Then you can add all the values into one big "values" string:

def values(active_records)
  active_records.map { |active_record| values_for(active_record) }.join(",")
end

For the insert part of the SQL you can use a similar code to the one used for the value extraction:

INSERT_REGEX = /(INSERT INTO .* VALUES) (.*)/

def insert_statement(active_record)
  sql = active_record.class.arel_table.create_insert.tap do |insert_manager|
    insert_manager.insert(
      active_record.send(:arel_attributes_with_values_for_create, active_record.attribute_names.sort)
    )
  end.to_sql
  INSERT_REGEX.match(sql).captures[0]
end

For the conflict part you can do the following:

def on_conflict_statement(conflict_fields)
  return ';' if conflict_fields.blank?

  "ON CONFLICT (#{conflict_fields.join(', ')}) DO NOTHING"
end

At the end just concatenate all of them together for execution:

def perform(active_records:, conflict_fields:)
  return if active_records.empty?

  ActiveRecord::Base.connection.execute(
    <<-SQL.squish
        #{insert_statement(active_records.first)}
        #{values(active_records)}
        #{on_conflict_statement(conflict_fields)}
    SQL
  )
end

You can check here the final solution.

This is the output for me - using my balance active record model and removing the piece of code that executes the SQL(ActiveRecord::Base.connection.execute...):

> puts BatchCreate.perform(active_records: [balance], conflict_fields: [:bank_account_id, :date])
INSERT INTO "balances" ("amount", "created_at", "currency", "date", "id", "bank_account_id", "updated_at") VALUES (0, '2018-11-28 15:42:44.312954', 'MXN', '2018-11-28', 15169, 26300, '2018-11-28 16:05:07.465402') ON CONFLICT (bank_account_id, date) DO NOTHING

Collectives™ on Stack Overflow

Using UPSERT PostgreSQL from Rails 4.2

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related