Upgrade PostgreSQL JSON column to JSONB?

postgresql jsonb
srlm picture srlm · Jan 21, 2015 · Viewed 7.9k times · Source

After upgrading to PostgreSQL 9.4, how do I convert all my JSON columns into JSONB columns?

I don't mind losing any duplicate keys and whitespace.

Answer

Tometzky picture Tometzky · Jan 21, 2015 
ALTER TABLE t ALTER COLUMN j TYPE jsonb USING j::text::jsonb;

Related questions

How to perform update operations on columns of type JSONB in Postgres 9.4

Looking through the documentation for the Postgres 9.4 datatype JSONB, it is not immediately obvious to me how to do updates on JSONB columns. Documentation for JSONB types and functions: http://www.postgresql.org/docs/9.4/static/functions-json.html http://www.postgresql.…

Read More
Query for array elements inside JSON type

I'm trying to test out the json type in PostgreSQL 9.3. I have a json column called data in a table called reports. The JSON looks something like this: { "objects": [ {"src":"foo.png"}, {"src":"bar.png"} ], "background":"background.png" } I would …

Read More
Explanation of JSONB introduced by PostgreSQL

PostgreSQL just introduced JSONB and it's already trending on hacker news. It would be great if someone could explain how it's different from Hstore and JSON previously present in PostgreSQL. What are its advantages and limitations and when should someone …

Read More