After upgrading to PostgreSQL 9.4, how do I convert all my JSON columns into JSONB columns?
I don't mind losing any duplicate keys and whitespace.
Add a comment
|
3 Answers
ALTER TABLE t ALTER COLUMN j TYPE jsonb USING j::text::jsonb;
Comments
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 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, 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.
Comments
In case someone looking for a solution with alembic (specifically, I use flask + alembic + postres 15.3):
import sqlalchemy as sa
from alembic import op
from sqlalchemy.dialects import postgresql
def upgrade():
op.alter_column('system_info', 'phones', type_=postgresql.JSONB, postgresql_using='phones::text::jsonb')
def downgrade():
op.alter_column('system_info', 'phones', type_=sa.JSON(), postgresql_using='phones::json')
Can't tell much about it, I'm no expert, but in my case the migration goes smoothly (which means I haven't noticed any data damage or even changing). Some details you can find here. I posted downgrade also because in alembic you need to have rollback strategy.
