In postgresql 9.4 the new JSONB was incorporated.
On a live DB in postgresql 9.3 I have a JSON column.
I want to migrate it to JSONB.
Assuming I migrated the DB first to 9.4 (using pg_upgrade). What do I do next?
Add a comment
|
2 Answers
ALTER TABLE table_with_json
ALTER COLUMN my_json
SET DATA TYPE jsonb
USING my_json::jsonb;
5 Comments
gregoltsov
Are there any benchmarks on how fast this is? Ideally with how that scales with the number of records.
gregoltsov
Asked this as a separate question.
Miguel Peniche
In Rails console:
ActiveRecord::Base.connection.execute('ALTER TABLE table_with_json ALTER COLUMN my_json SET DATA TYPE jsonb USING my_json::jsonb')
Boris Barroso
This doesn't work when migrating in postgresql 9.5
Kalpit Champanery
If anyone using old sqlalchemy-utils and is wondering about JSONB not as sqlalchemy-util data type, you can use from sqlalchemy.dialects.postgresql import JSONB in your model. and migrate DB as above answer
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.
3 Comments
OzBarry
Out of curiousity, why would you use
reversible do ... end rather than def up ... end and def down ... end?
Alexander Popov
@OzBarry because of convention and because using
change in combination with reversible is the "recommended" way. Have a look at edgeguides.rubyonrails.org/…, in particular 3.8 Using the change Method, 3.9 Using reversible, 3.10 Using the up/down Methods
Lee Dykes
Could you add this answer here? stackoverflow.com/questions/50024841/… . That way it will be the exact right answer as this question didn't actually mention rails