How to migrate data from an associated table's column to an hstore column?

Question

I'm working on a project where localisation was done by creating associated *_locales tables with a locale and name field. I am migrating this over to use an hstore column (using Postgres) but I don't quite have the syntax right.

The structure is now like this

table: thingy_locales
  locale :string
  name   :string
  thingy_id : integer


table: thingies
  name_translations :hstore

In a migration I wish to move all data from the thingy_locales table into the name_translations field with an key of 'en' (as currently there are only 'en' locales in the thingy_locales table.)

so I've tried this

execute "UPDATE thingies t SET name_translations=(select (\"'en'\" => \"'|name|'\")::hstore from thingy_locales where thingy_id = t.id);"

but that gives me

PG::UndefinedColumn: ERROR:  column "'en'" does not exist
LINE 1: ...ort_categories loc SET name_translations=(select ("'en'" => ...
                                                             ^

What have I done wrong?

Answer 1

Okay I got it to work.

execute "UPDATE thingies t SET name_translations=hstore('en', (select name from thingy_locales where thingy_id = t.id));"

does the job perfectly

Answer 2

I don't know of an automatic SQL command to do it, but you probably want to do multiple migrations:

• Add the hstore column to thingies
• Iterate over all of the thingy_locales, reading them into a hash/hashes. Then do Thingy.create!(name_translations: {'en' => hash})
• Drop thingy_locales

Migrations in Rails can have any arbitrary code in them. Since this is an instance where you will be transforming the data, doing it in Ruby is probably your safest bet.

If speed is a concern, then you may need to go for an optimized SQL query, but frankly if you aren't worried about speed, don't trouble yourself.

Answer 3

Actually, just looking at your code again, assuming that you have this association:

class Thingy
  has_many :thingy_locales
end

class ThingyLocale
  belongs_to :thingy
end

it seems like what you want to do something like this:

Thingy.all.each do |thingy|
  name = thingy.thingy_locales.where(locale: 'en').first.select(:name)
  thingy.name_translations = {'en' => name}
  thingy.save!
end

Answer 4

Don't try to grind a ton of data through Ruby, do it inside the database. Two approaches immediately come to mind:

1. Use a correlated subquery in an UPDATE:

   connection.execute(%q{
     update thingies
     set name_translations = (
       select hstore(array_agg(locale), array_agg(name))
       from thingy_locales
       where thingy_id = thingies.id
     )
   })
   

2. JOIN to a derived table in an UPDATE:

   connection.execute(%q{
     update thingies
     set name_translations = dt.h
     from (
       select hstore(array_agg(locale), array_agg(name)) as h, thingy_id
       from thingy_locales
       group by thingy_id
     ) as dt
     where id = dt.thingy_id
   })
   

The core of both is to use the hstore(text[], text[]) function to build the hstore and the array_agg function to build the key and value arrays that hstore(text[], text[]) wants.

Don't be afraid to throw SQL into connection.execute calls in your migrations. Pretending that your database is too dumb to do anything interesting may be ideologically pure in a Rails application but it is a non-productive and unprofessional attitude. You'll be better served in the long run by learning SQL and how your databases work.