Is there a way to modify an existing column of HSTORE datatype (that has data) to JSON datatype in a Postgres database?

up vote 5 down vote accepted

You can simple use hstore_to_json function to get a json out of a hstore. To change the table you can use the ALTER TABLE command.

Example:

ALTER TABLE table_name
    ALTER your_column_name TYPE json USING hstore_to_json(your_column_name);

There is hstore_to_jsonb too if you want jsonb instead of json.

  • 2
    Thought it's worth mentioning. This only works for Posgresql 9.3 version and above. – DUnkn0wn1 Sep 14 '16 at 17:36
  • Do you know whether there any performance benefits of doing so? – Milan Velebit Sep 21 at 12:53

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.