postgresql 将 JSON 迁移到 JSONB
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28202158/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
postgresql migrating JSON to JSONB
提问by Boaz
In postgresql 9.4 the new JSONB was incorporated.
在 postgresql 9.4 中加入了新的 JSONB。
On a live DB in postgresql 9.3 I have a JSON column.
在 postgresql 9.3 中的实时数据库上，我有一个 JSON 列。
I want to migrate it to JSONB.
我想将它迁移到 JSONB。
Assuming I migrated the DB first to 9.4 (using pg_upgrade). What do I do next?
假设我首先将数据库迁移到 9.4（使用 pg_upgrade）。我接下来该怎么做？
回答by Marth
ALTER TABLE table_with_json
ALTER COLUMN my_json
SET DATA TYPE jsonb
USING my_json::jsonb;
回答by Alexander
In the context of Rails, here is an ActiveRecord migration alternative:
在 Rails 的上下文中，这是一个 ActiveRecord 迁移替代方案：
def change
reversible do |dir|
dir.up { change_column :models, :attribute, 'jsonb USING CAST(attribute AS jsonb)' }
dir.down { change_column :models, :attribute, 'json USING CAST(attribute AS json)' }
end
end
I don't know how this compares to the accepted answer performance-wise, but I tested this on a table with 120 000 records, each record having four jsoncolumns and it took me about a minute to migrate that table. Of course, I guess it depends on how complex the jsonstructure is.
我不知道这与接受的答案性能相比如何，但我在一个包含 120 000 条记录的表上进行了测试，每条记录有四json列，我花了大约一分钟来迁移该表。当然，我想这取决于json结构的复杂程度。
Also, notice that if your existing records have a default value of {}, you have to add to the above statements default: {}, because otherwise you'll have jsonbcolumns, but the default value will remain as '{}'::json.
另外，请注意，如果现有记录的默认值为{}，则必须添加到上述语句中default: {}，否则您将拥有jsonb列，但默认值仍为'{}'::json。
