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 move the field payloadfrom jsonto hstore.

Do the following:

change_column :user_actions, :payload, :hstore

The result is 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 prompt USINGand what is the best way to perform this migration without data loss?

Answer 1

TIP: Specify a USING expression to perform the conversion

Actually format:

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:

fooobar.com/questions/53720 / ...

Answer 2

, postgresql JSON HSTORE . , JSON , :

  def self.up
    execute <<-SQL
        CREATE OR REPLACE FUNCTION my_json_to_hstore(json)
              RETURNS hstore
              IMMUTABLE
              STRICT
              LANGUAGE sql
            AS $func$
              SELECT hstore(array_agg(key), array_agg(value))
              FROM   json_each_text($1)
            $func$;
        SQL
    change_column :user_actions, :payload, 'hstore USING my_json_to_hstore(payload)'
    end

  def self.down
    change_column :user_actions, :payload, 'json USING CAST(payload AS json)'
    execute "DROP FUNCTION my_json_to_hstore(json)"
  end

pozs postgresql : JSON HSTORE Postgres 9.3+?

Answer 3

, . parameters script:

def up
  add_column :user_actions, :parameters, :hstore

  UserAction.find_each do |o|
    o.parameters = o.payload
    o.save!
  end

  remove_column :user_actions, :payload
end

def down
  add_column :user_actions, :payload, :json

  UserAction.find_each do |o|
    o.payload = o.parameters
    o.save!
  end

  remove_column :user_actions, :parameters
end