среда, 1 мая 2019 г.

Inplace column type alteration in Postgres: jsonb <-> hstore

In one of my projects I had to migrate localization resources, stored in jsonb to a plain hstore to reduce storage space and complexity.

Here comes an example of the direct and reverse DDL operations:


ALTER TABLE resources
    ALTER COLUMN name TYPE hstore
    USING hstore('ru', (name->'translate'->>'ru'))||hstore('en', (name->'translate'->>'en'));



ALTER TABLE resources
    ALTER COLUMN name TYPE jsonb
    USING json_build_object('translate', json_build_object('ru', name->'ru', 'en', name->'en'));