Converting JSON to JSONB in PostgreSQL

Converting a column from json to jsonb and vice versa is fairly straightforward in PostgreSQL.

Implicit casting is supported, a USING clause does not need to be provided.

#Converting JSON to JSONB

ALTER TABLE my_table
    ALTER COLUMN data
        SET DATA TYPE json;

#Converting JSONB to JSON

ALTER TABLE my_table
    ALTER COLUMN data
        SET DATA TYPE jsonb;

#Example

-- Create a table with a json column named "data"
CREATE TABLE my_table(data json);

-- Insert a row
INSERT INTO my_table(data) VALUES ('{"message": "Hello, World!"}');

-- Convert the column from json to jsonb
ALTER TABLE my_table
    ALTER COLUMN data
        SET DATA TYPE jsonb;

SELECT * FROM my_table; -- {"message":"Hello, World!"}

-- Convert the column back from jsonb to json
ALTER TABLE my_table
    ALTER COLUMN data
        SET DATA TYPE json;

SELECT * FROM my_table; -- {"message":"Hello, World!"}