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?

Spleeny

did you try hstore_to_json(hstore) ? – Lisa

yes but I'm not sure which default value should I set in order to perform the conversion: ALTER TABLE my_table ALTER COLUMN h_store SET DEFAULT '{}'::JSON and it refuses to proceed, SET DEFAULT '{}' and I'm getting string errors. – Spleeny

mm, I just dropped the default and apparently it's going, mind writing an answer so I can accept it? Thanks. – Spleeny

Try alter table my_table alter column h_store_column type json using hstore_to_json(h_store_column) – Pleione

a_horse_with_no_name should do the honours and write an answer, his answer is more complete – Lisa

If you have a gist index on that column, you will need to drop it before converting the column. – Ossification

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.

Whopping