I really do like PostgreSQL’s
hstore type, espcially when I have some configuration properties that I don’t need to query or index. But ran into a problem when I needed to migrate data. I needed to move a column to an hstore within the same row (and back again).
Knowing that migrating data with ruby models is fragile and WILL break when you change your models – I wanted the data munging to be done in raw SQL.
Say I have a
url column in
my_table. I want to move that to the ‘url’ key in a new
hstore column called (helpfully)
my_hstore. This migration will add the new column, migrate the data and drop the old column. If nervous about that and want to keep the column around just delete the
First, generate the migration:
rails generate migration move_url_to_my_hstore
Then, update it to add the column, migrate the data, and drop the column. Then reverse the entire process in the
class MoveUrlToMyHstore < ActiveRecord::Migration[5.0] def up add_column :my_table, :my_hstore, :hstore execute <<~END UPDATE my_table SET my_hstore = hstore('url', subquery.url) FROM (SELECT id, url FROM my_table) as subquery WHERE my_table.id=subquery.id; END remove_column :my_table, :url, :string end def down add_column :my_table, :url, :string execute <<~END UPDATE my_table SET url = subquery.turl FROM (SELECT id, my_hstore->'url' as turl FROM my_table) as subquery WHERE my_table.id=subquery.id; END remove_column :my_table, :my_hstore end end
Hope this is helpful to someone out there.