0 comment

PostgreSQL: Convert an hstore column to jsonb

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:

Let’s see, if it looks OK:


Fine. Next step is the magic column conversion:

Another look:

And we get:

Mission accomplished.

Leave a Reply

Required fields are marked *.