Activerecord-import upsert with non unique columns into postgres

Question

I am trying to use activerecord-import to import data and I ran into an issue. In case the row already exists it needs to be updated else it needs to be created. The unique rows in the table are defined by a combination of two columns where each column value might not be unique in the table. How would I go about using the on_duplicate_key_update's conflict_target since the columns are not unique columns.

Now for the details. I have a model called Order, below is a simplified version of the columns in the Order model.

id
order_number
sku_number
quantity

The unique rows in the database are identified by a combination of order_number and sku_number. The id column is auto incremented. Normally I could do something like the following if I have an unique column as an identifier. So in the example below I would use the id column as the conflict_target assuming the id column was the unique identifier.

order = Order.create(order_number: '1', sku_number: '1', quantity: 100)
order.quantity = 200
Order.import [order], on_duplicate_key_update: {conflict_target: [:id], columns: [:quantity]}

and that would update the quantity because the order already exists.

But what I need is something like

Order.import [order], on_duplicate_key_update: {conflict_target: [:order_number, :sku_number], columns: [:quantity]}

Where the order_number and the sku_number would uniquely identify the conflict row.

But that fails since Postgres is expecting an unique column for the conflict_target.

Is there some way to call on_duplicate_key_update with non unique columns i.e. order_number and sku_number?

Any help would be greatly appreciated.

Pras · Accepted Answer · 2016-10-14 03:50:00Z

6

I figured it out. I am putting down the solution in case someone else runs into the same problem.

I ended up creating a unique index on Order

class AddUniqueKeyConstraintToOrders < ActiveRecord::Migration
  def change
    add_index :orders, [:order_number, :sku_number], unique: true
  end
end

activerecord-import supports using an index as the constraint. So I ended up doing something like

order = Order.last
order.quantity = 200
Order.import [order], on_duplicate_key_update: {conflict_target: [:order_number, :sku_number], index_name: :index_orders_on_order_number_and_sku_number, columns: [:quantity]}

Where index_orders_on_order_number_and_sku_number is the name of the index created by the migration. Which updated the quantity from the previous value to 200 without creating an additional record.

edited Oct 14, 2016 at 3:50

answered Oct 12, 2016 at 12:13

Pras

1892 silver badges15 bronze badges

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

Comments

Josip Đuraković · Accepted Answer · 2017-12-13 13:49:51Z

An other way to do the same is to create an CONSTRAIANT as unique combination of multiple columns. In the migration file after the create_table we can:

create_table :board_statistics do |t|
    t.string :hostname, null: false
    t.string :slot
    …

end

Create just an unique index

add_index :board_statistics, [:hostname, :slot, :serial_number], unique: true

or create an constraint

execute <<-SQL                                                                                                        
       ALTER TABLE board_statistics  
        ADD CONSTRAINT one_card_xan_slot UNIQUE (hostname, slot, serial_number);
 SQL

Constraint can be used in Activerecord-import gem like:

BoardStatistic.import columns, on_duplicate_key_ignore: {constraint_name: :one_card_xan_slot}

Collectives™ on Stack Overflow

Activerecord-import upsert with non unique columns into postgres

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related