将JSON列类型迁移到HSTORE列类型

Question

我目前有以下数据库架构：

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

我希望将json中的payload字段迁移到hstore中。

步骤如下：

change_column :user_actions, :payload, :hstore

以下是错误信息的结果：

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

不确定如何使用USING提示符以及在不丢失任何数据的情况下进行此迁移的最佳方法？

- Pierre-Louis Gottfrois

---

这个例子有帮助吗？（第 F.16.6 节兼容性） - DrColossos

这可能是可行的，@DrColossos 的示例展示了 postgres 使用子句。我不太了解 ruby/activerecord，但我找到了这个线程：https://github.com/rails/rails/issues/11062，它表明 1）你不能通过 activerecord 获得使用子句，2）解决方法是直接转到 SQL。 - Greg

谢谢，实际上我选择了一个简单的解决方案，重命名列并进行简单的迁移 o.new_field = o.payload，其中 new_field 是一个 hstore。 - Pierre-Louis Gottfrois

Answer 1

提示：指定一个USING表达式进行转换。

实际格式为：

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

所以在你的情况下：

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

参考资料：

https://dev59.com/2GYs5IYBdhLWcg3wE_ve#25032193

- Taimoor Changaiz

---

这个不起作用。Postgres 无法将 JSON 强制转换为 HSTORE，除非使用自定义函数。请参见我的答案，它可以正常工作。 - l85m

解决了我遇到的问题。 - W.M.

Answer 2

Taimoor Changaiz的回答部分不正确，因为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转换为Postgres 9.3+中的HSTORE？

- l85m

Answer 3

我为您提供一个简单但有效的解决方案。创建一个名为parameters的新列并执行一个简单的迁移脚本：

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

- Pierre-Louis Gottfrois