Migrate existing data when altering the column type from string to json I have a table with an existing column type varchar. I need to convert this column type to new type jsonb. The problem is if the column has already data 'str1:str2' then after converting the type to jsonb column has invalid JSON string 'str1:str2'.
I want to migrate the existing string 'str1:str2' into the proper JSON string with keys as '{&quot;key1&quot;:&quot;str1&quot;,&quot;key2&quot;:&quot;str2&quot;}' before/while converting the column type.
Is it possible to do it using ALTER TABLE ALTER COLUMN....Using function() clause or in some other way.
Rather than making up any keys, why not convert that to a json array?
alter table the_table
  alter column the_column type jsonb 
  using to_jsonb(string_to_array(the_column, ':'));

That would turn str1:str2 into [&quot;str1&quot;, &quot;str2&quot;]
If you really need to make up some (numbered keys), you will have to write a helper function:
create function make_json_keys(p_input text)
returns jsonb
as
$$
  select jsonb_object_agg('key'||idx, val)
  from unnest(string_to_array(p_input, ':')) with ordinality as t(val, idx);
$$
language sql
immutable;  

That can be used in the USING expression:
alter table the_table
  alter column the_column type jsonb 
  using make_json_keys(the_column);