postgresql migrating JSON to JSONB

postgresql jsonb

Boaz · Jan 28, 2015 · Viewed 21.9k times · Source

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?

Answer

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