Migrate JSON column type to HSTORE column type

Question

I currently have the following db schema:

create_table :user_actions do |t|
  t.integer  :emitter_id
  t.string   :emitter_type
  t.integer  :target_id
  t.string   :target_type
  t.json     :payload
  t.timestamps
end

And I would like to migrate the payload field from json to hstore.

Doing the following:

change_column :user_actions, :payload, :hstore

Result in the following error message:

PG::DatatypeMismatch: ERROR:  column "payload" cannot be cast automatically to type hstore
HINT:  Specify a USING expression to perform the conversion.

Not sure how to use the USING hint and what's the best way to do this migration without loosing any data ?

Answer 1

HINT: Specify a USING expression to perform the conversion

Actually the format is:

change_column :user_actions, :payload, '[type_to_which_you_want_to_change] USING CAST(data AS [type_to_which_you_want_to_change])'

So in your case:

change_column :user_actions, :payload, 'hstore USING CAST(payload AS hstore)'

Reference:

https://stackoverflow.com/a/25032193/1222852