Formatting hstore column Postgres

Question

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.

Answer 1

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')

Answer 2

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.

Answer 3

You can use some function to make it generic:

Let's get some example:

 select '{"a":1,"b":2}'::json;
┌───────────────┐
│     json      │
├───────────────┤
│ {"a":1,"b":2} │
└───────────────┘
(1 row)

Back to text:

select '{"a":1,"b":2}'::json::text;
┌───────────────┐
│     text      │
├───────────────┤
│ {"a":1,"b":2} │
└───────────────┘
(1 row)

Now, remove the undesired tokens {}" with a regex:

select regexp_replace('{"a":1,"b":2}'::json::varchar, '["{}]+', '', 'g');
┌────────────────┐
│ regexp_replace │
├────────────────┤
│ a:1,b:2        │
└────────────────┘
(1 row)

and you can wrap it into a function:

create function text_from_json(json) returns text as $$select regexp_replace($1::text, '["{}]+', '', 'g')$$ language sql;
CREATE FUNCTION

Testing the function now:

tsdb=> select text_from_json('{"a":1,"b":2}'::json);
┌────────────────┐
│ text_from_json │
├────────────────┤
│ a:1,b:2        │
└────────────────┘