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?

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. — John Smith
– John Smith, Commented Nov 16, 2015 at 10:06
mm, I just dropped the default and apparently it's going, mind writing an answer so I can accept it? Thanks. — John Smith
– John Smith, Commented Nov 16, 2015 at 10:11
Try alter table my_table alter column h_store_column type json using hstore_to_json(h_store_column) — user330315
– user330315, Commented Nov 16, 2015 at 10:11
If you have a gist index on that column, you will need to drop it before converting the column. — BillRobertson42
– BillRobertson42, Commented Aug 12, 2017 at 3:50

toxaq · Accepted Answer · 2020-05-22 05:31:58Z

4

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.

Collectives™ on Stack Overflow

postgres: how to convert hstore to JSON datatypes

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related