jsonpostgresqljsonbpostgresql-9.4hstore

Migrate JSON to HSTORE

I'm using PostgreSQL 9.4.

Initially I had on my USERS table a DETAILS field that was an HSTORE field type.

I updated that column type from HSTORE to JSONB very easily using:

ALTER TABLE users ALTER COLUMN details TYPE jsonb USING CAST(details AS jsonb)

The issue I'm facing now is that I have to write a rollback but I was not able to find a way to cast from JSONB to HSTORE.

I've tried using:

ALTER TABLE users ALTER COLUMN details TYPE hstore USING CAST(details AS hstore)

And got:

ERROR: cannot cast type jsonb to hstore

Can someone help me with this please?

Solution

  • If the jsonb values are in the simple form of pairs {"key":"value",...}, you can use this function:

    create or replace function simple_jsonb_to_hstore(jdata jsonb)
returns hstore language sql immutable
as $$
    select hstore(array_agg(key), array_agg(value))
    from jsonb_each_text(jdata)
$$;

ALTER TABLE users ALTER COLUMN details TYPE hstore USING simple_jsonb_to_hstore(details);