I’m involved in a tech scouting project, where I designed a PostgreSQL database schema using an hstore column to store additional a priori unspecified data to every record in that table. For some historical reason I decided to use an hstore column instead of one of the much more versatile JSON column types.

Historical priorities change and so they did in that project. Now I wanted to use that column as JSONB. As always in SQL columns are changed with an ALTER TABLE … ALTER COLUMN statement. In this case the conversion of the content is a bit more complex, but giving PostgreSQL some hint about what we expect does the job.

First let’s create a table to play with and insert one row of data:

CREATE TABLE hstore_data (data hstore);
INSERT INTO hstore_data (data) VALUES ('key1 => "value1", key2 => "value2"');

Let’s see, if it looks OK:

SELECT * FROM hstore_data;

Yields:

                data                
------------------------------------
 "key1"=>"value1", "key2"=>"value2"
(1 row)

Fine. Next step is the magic column conversion:

ALTER TABLE hstore_data ALTER COLUMN data TYPE JSONB USING CAST(data as JSONB);

Another look:

SELECT * FROM hstore_data;

And we get:

                 data                 
--------------------------------------
 {"key1": "value1", "key2": "value2"}
(1 row)

Mission accomplished.