postgresql migrating JSON to JSONB
In the context of Rails, here is an ActiveRecord migration alternative:
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 json
columns and it took me about a minute to migrate that table. Of course, I guess it depends on how complex the json
structure is.
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 jsonb
columns, but the default value will remain as '{}'::json
.
ALTER TABLE table_with_json
ALTER COLUMN my_json
SET DATA TYPE jsonb
USING my_json::jsonb;