Search code examples
jsonpostgresqlhstore

postgres: how to convert hstore to JSON datatypes

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?

Solution

  • 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.