postgres: how to convert hstore to JSON datatypes

Question

I'm trying to write a migration to convert an existing hstore column to JSON (not JSONB).

I tried different solutions json USING cast(hstore_column as json), some functions found over github, but nothing really worked out.

Main issue is that there's no direct conversion, second is that even if I cast the column to text as an intermediate step I need to change the default column value to json as well.

Anyone already did this?

Answer 1

You can simply use

alter table my_table alter column h_store_column type json using hstore_to_json(h_store_column)

Of course you will need to drop any defaults set on the column that don't align with the json data type first.