CODEWITHSUNDEEP
ruby-on-railspostgresqlsql-updatequery-optimizationsanitization
LikeMaBell
LikeMaBell

Reputation: 1609

Single Postgres query to update many records using a local hash/array

I want to use a single query to update many records in my Postgres database using a Ruby hash or array, rather than having to iterate through each record and call a separate update.

# {:id => :color}
my_hash = {
  1 => 'red',
  2 => 'blue',
  3 => 'green'
}

How I don't want to do it because it does three serial queries:

my_hash.each do |id, color|
  MyModel.where(id: id).update_all(color: color)
end

How I do want to do it:

MyModel.connection.execute <<-SQL
  UPDATE my_models
    SET color=something
    FROM somehow_using(my_hash)
    WHERE maybe_id=something
SQL

Upvotes: 6

Views: 1618

Answers (4)

David Moles
David Moles

Reputation: 51239

You can do this with the Postgres VALUES function:

UPDATE my_models
   SET color = temp.color
  FROM (SELECT *
          FROM (VALUES (1, 'red'), (2, 'blue'), (3, 'green'))
            AS t(id, color)
       ) AS temp
 WHERE my_models.id = temp.id

This works reasonably well even with hundreds of values. To do this in Ruby from a hash, use something like:

values = my_hash.map { |id, color| "(#{id}, '#{color}')" }.join(', ')
# => (1, 'red'), (2, 'blue'), (3, 'green')

stmt = <<~SQL
UPDATE my_models
   SET color = temp.color
  FROM (SELECT *
          FROM (VALUES #{values})
            AS t(id, color)
       ) AS temp
 WHERE my_models.id = temp.id
SQL

MyModel.connection.exec_update(stmt)

Note though that you really don't want to do this with user input unless you can sanitize it first or you like SQL injection attacks. I imagine something like this would work, although I haven't actually tried it:

values = my_hash.keys.map { |id| "(#{id}, ?)" }.join(', ')
# => (1, ?), (2, ?), (3, ?)

sql = <<~SQL
UPDATE my_models
   SET color = temp.color
  FROM (SELECT *
          FROM (VALUES #{values})
            AS t(id, color)
       ) AS temp
 WHERE my_models.id = temp.id
SQL

stmt = ActiveRecord::Base.sanitize_sql([sql, *my_hash.values])
MyModel.connection.exec_update(stmt)

Upvotes: 0

wbharding
wbharding

Reputation: 4583

For readers that might encounter this question in 2020+, it looks like upsert_all does what the OP wanted in Rails 6:

MyModel.upsert_all([{ id: 1, color: "red" }, { id: 2, color: "blue" }, { id: 3, color: "green" }])

Will generate something like

# Bulk Insert (26.3ms) INSERT INTO `my_models`(`id`,`color`)
# VALUES (1, 'red')...
# ON DUPLICATE KEY UPDATE `color`=VALUES(`color`)

Example inspired by this blog.

Upvotes: 1

LikeMaBell
LikeMaBell

Reputation: 1609

Another solution is to concatenate a series of updates into a single string and send that all at once. Downside is that it sends more data across the wire, but on the other hand PG doesn't have to deserialize and process the JSON.

ActiveRecord::Base.connection.execute(
  my_hash.collect{|id,color| "UPDATE my_models SET color=#{color} WHERE id=#{id};"}.join('')
)
# And don't forget to sanitize the :id and :color values

Upvotes: 0

klin
klin

Reputation: 121919

You can use case:

update my_models
    set color = case id
        when 1 then 'red'
        when 2 then 'blue'
        when 3 then 'green'
    end;

or save the hash in a separate table:

create table my_hash (id int, color text);
insert into my_hash values
    (1, 'red'),
    (2, 'blue'),
    (3, 'green');

update my_models m
    set color = h.color
    from my_hash h
    where h.id = m.id;

One more option, if you know the way to select the hash as jsonb:

with hash as (
    select '{"1": "red", "2": "blue", "3": "green"}'::jsonb h
    )
update my_models
    set color = value
    from hash, jsonb_each_text(h)
    where key::int = id;

OP ruby-fying klin's third option:

sql = <<-SQL
with hash as (
  select '#{my_hash.to_json}'::jsonb h
)
update my_models
  set color = value
  from hash, jsonb_each_text(h)
  where key::int = id;
SQL

ActiveRecord::Base.connection.execute(sql)

Upvotes: 2

Related Questions