Migration script to convert event data & metadata from bytea to jsonb

[slashdotdash]

Support for jsonb data type has been added in #86. It would be useful to provide a data migration script to convert existing (JSON encoded) bytea event data and metadata to jsonb.

[mquickform]

Posting it her for posterity, might be useful (postgres)

ALTER TABLE events 
  ALTER COLUMN data TYPE jsonb USING convert_from(data, 'UTF8')::jsonb,
  ALTER COLUMN metadata TYPE jsonb USING convert_from(metadata, 'UTF8')::jsonb;

[Fire-Dragon-DoL]

As a side note, it should be performed for snapshots table too.

[Fire-Dragon-DoL]

In case you don't have convert_from available in your postgres installation (I don't have it in mine and I'm not sure where it's coming from):

alter table events alter column metadata type jsonb using (metadata #>> '{}')::jsonb, alter column data type jsonb using (data #>> '{}')::jsonb;
alter table snapshots alter column metadata type jsonb using (metadata #>> '{}')::jsonb, alter column data type jsonb using (data #>> '{}')::jsonb;