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).
But the command failed with the below error.
ERROR: invalid input syntax for type json
DETAIL: Token “This” is invalid.
CONTEXT: JSON data, line 1: This…
Note : The message column has a set of words with spaces like below.
“This is a message”
LikeLike
But the command failed with the below error.
ERROR: invalid input syntax for type json
DETAIL: Token “This” is invalid.
CONTEXT: JSON data, line 1: This…
Note : The message column has a set of words with spaces like below.
“This is a message”
LikeLike