PostgreSQL 数据库中 JSON 到 JSONB 的迁移

Question

在 postgresql 9.4 中，新的 JSONB 已经被纳入。在 postgresql 9.3 的实时数据库中，我有一个 JSON 列。我想将其迁移到 JSONB。假设我先使用 pg_upgrade 将数据库迁移到 9.4，接下来该怎么做？

- Boaz

Answer 1

ALTER TABLE table_with_json
  ALTER COLUMN my_json
  SET DATA TYPE jsonb
  USING my_json::jsonb;

- Marth

---

这个有多快的基准测试吗？最好能够显示随着记录数量增加，速度如何扩展。 - gregoltsov

将此作为单独的问题提出。 - gregoltsov

在Rails控制台中： ActiveRecord::Base.connection.execute('ALTER TABLE table_with_json ALTER COLUMN my_json SET DATA TYPE jsonb USING my_json::jsonb') - Miguel Peniche

在迁移PostgreSQL 9.5时，这个不起作用。 - Boris Barroso

如果有人正在使用旧版的sqlalchemy-utils，并且对于JSONB不是sqlalchemy-util数据类型感到困惑，您可以在模型中使用from sqlalchemy.dialects.postgresql import JSONB。并按照上面的答案迁移数据库。 - Kalpit Champanery

Answer 2

在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

我不知道这种方法在性能方面与被接受的答案相比如何，但是我在一个有120,000个记录的表上测试了一下，每个记录都有四个json列，迁移该表花费了大约一分钟的时间。当然，我猜这取决于json结构的复杂程度。

另外，请注意，如果您现有的记录具有默认值{}，则必须在上述语句中添加default: {}，否则您将拥有jsonb列，但默认值仍将保持为'{}'::json。

- Alexander Popov

---

出于好奇，你为什么要使用reversible do ... end而不是def up ... end和def down ... end？ - OzBarry

@OzBarry，因为惯例和使用change与reversible结合的方式是“推荐”的方式。请参阅http://edgeguides.rubyonrails.org/active_record_migrations.html#using-the-change-method，特别是3.8使用更改方法，3.9使用可逆性，3.10使用上/下方法。 - Alexander Popov

你能在这里添加这个答案吗？ https://dev59.com/N6rka4cB1Zd3GeqPkubN。这样它将是完全正确的答案，因为这个问题实际上并没有提到Rails。 - Lee Dykes