I'm trying to find the best way to format a hstore column (see screenshot) my goal is to have the same format based on the screenshot as the "updated_column. I was thinking about a case statement like :

Case when json_column -> id then 'id:'

any suggestion would be appreciated.

CodePudding user response：

Migration approach:

• Add new column with type text like you want it
• make sure new data directly enters the new column as the string you want (pre-formatted at the backend)
• Create a migration function that converts json column data batchwise into your new string table. You can use postgres replace/.. operations to reformat it. You can also use an external python script/...
• remove the json column after the migration is done

Let me see what / how you have tried and then we can see how to improve/solve your issues.

CodePudding user response：

So I think i found a temporary solution that will work, but I think like @Bergi mentioned a view might be more appropriate.

For now I will just use something like:

   concat(concat(concat(concat('id',':',column -> 'id')
    ,' ','auth_id',':',column -> 'auth_id')
    ,' ','type',':',column -> 'type')
    ,' ','transaction',':',column -> 'transaction')