POSTGRESQL converting column to JSON and taking care of the old data-CodePudding

I have a column which is of integer type and I wanted to alter my column and convert it to json type while also taking care of the old data present.

So let's say I have this old data: 99
And I want to convert it to : { id: 99 }

I'm trying this alter statement:

ALTER TABLE mytable
ALTER COLUMN "idColumn" TYPE json
USING CONCAT('{id:', "idColumn", '}')::json;

But I'm having an error regarding the invalid column token.

Any hints?

CodePudding user response：

There are a few problems with quotation marks in your query. Check this out, it works:

drop table if exists mytable;
create table mytable (id integer, idColumn integer, name varchar(100));
insert into mytable values (1, 99, 'jasiek');
ALTER TABLE mytable
ALTER COLUMN idColumn TYPE json
USING CONCAT('{"id":', idColumn, '}')::json;
SELECT idColumn::TEXT FROM mytable;

CodePudding user response：

The root cause of your problem is, that the key id needs to be enclosed in double quotes to be valid json, e.g. concat('{"id":', "idColumn", '}').

Creating valid JSON values, is easier done using json_build_object

ALTER TABLE mytable
  ALTER COLUMN "idColumn" TYPE json
  USING json_build_object('id', "idColumn");

But jsonb is recommended over json, so ideally it would be:

ALTER TABLE mytable
  ALTER COLUMN "idColumn" TYPE jsonb
  USING jsonb_build_object('id', "idColumn");

You should also avoid quoted identifiers, they are much more trouble in the long run, than they are worth it.