Convert TEXT json to JSON/JSONB data type in PostgreSQL


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).

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s