postgresql migrating JSON to JSONB

PostgresqlDdlJsonb

Postgresql Problem Overview


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?

Postgresql Solutions


Solution 1 - Postgresql

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

Solution 2 - Postgresql

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.

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionBoazView Question on Stackoverflow
Solution 1 - PostgresqlMarthView Answer on Stackoverflow
Solution 2 - PostgresqlAlexander PopovView Answer on Stackoverflow