Convert TEXT json to JSON/JSONB data type in PostgreSQL

Posted on by Edward Samuel Pasaribu

PostgreSQL has native support for JSON data type. There are 2 data types for JSON:

  • JSON
  • JSONB

The difference between JSON and JSONB data type are:

  • Internal structure of JSON is plain text. So, it will be faster to write and slower to read.
  • Internal structure of JSONB is binary. So, it will be slower to write and faster to read.
  • JSONB has indexing support.
  • JSON was introduced in PostgreSQL 9.2 and JSONB was introduced in PostgreSQL 9.4.

Migrate TEXT data type to JSON

ALTER TABLE table_name
    ALTER COLUMN column_name TYPE JSON USING column_name::JSON;

Migrate TEXT data type to JSONB

ALTER TABLE table_name
    ALTER COLUMN column_name TYPE JSONB USING column_name::JSONB;

Bonus

Event PostgreSQL JSON is faster than MongoDB (http://www.enterprisedb.com/postgres-plus-edb-blog/marc-linster/postgres-outperforms-mongodb-and-ushers-new-developer-reality).