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 ?

Does [this example](http://www.postgresql.org/docs/9.3/static/hstore.html#AEN149208) help? (Section F.16.6. Compatibility) — DrColossos, Jun 17 '14 at 13:12
this is probably doable @DrColossos example shows the postgres using clause. I don't know much about ruby/activerecord, I did find this thread: https://github.com/rails/rails/issues/11062 which indicates that 1) you can't get to a using clause through activerecord and 2) the work around is going straight to sql. — Greg, Jun 17 '14 at 13:20
Thx, actually went for a simple solution, rename column and do a simple migration `o.new_field = o.payload` where `new_field` is a `hstore` — Pierre-Louis Gottfrois, Jun 17 '14 at 15:41

score 12 · Accepted Answer · edited May 23 '17 at 10:30

12

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

edited May 23 '17 at 10:30

Community

1
1

answered Nov 19 '14 at 10:29

Taimoor Changaiz

10,250
4
49
53

1

This doesn't work. Postgres can't cast JSON to HSTORE without using a custom function. See my answer which does work. – l85m Feb 15 '16 at 00:03
Solved an issue I was facing. – W.M. Sep 12 '17 at 18:03

score 3 · Answer 2 · edited May 23 '17 at 12:26

Taimoor Changaiz's answer is partially incorrect since postgresql cannot cast JSON to HSTORE without using a custom function. Assuming your JSON is not nested, here's what will work:

  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

Credit to pozs for his custom postgresql function here: Casting JSON to HSTORE in Postgres 9.3+?

score 2 · Answer 3 · answered Jun 17 '14 at 15:43

Went for a simple but working solution. Create a new column named parameters and do a simple migration 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

Migrate JSON column type to HSTORE column type

3 Answers3