Updating a column from a varchar to jsonb

Question

I'm trying to update a column from a varchar type column into a JSON but the column is already filled in with chars that are numeric or string or a hash.

How can I cast them to turn into a JSON next with TO_JSON?

What I’ve tried

ALTER TABLE data_copy1 ADD COLUMN data2 jsonb;
UPDATE data_copy1 set data2= to_json(data);
ALTER TABLE data_copy1 DROP COLUMN data;
ALTER TABLE data_copy1 RENAME COLUMN data2 TO data;

This returns as result:

“39.9”
“32.2”
“Paso 5”
"{\"cam_in\": 0.6, \"cam_out\": 0.6}”

The numeric values are also taken as strings and I want them into integer, float, etc. And I’m expecting the same column to be like this:

39.9 
32.2
“paso 5 ”
"{\"cam_in\": 0.6, \"cam_out\": 0.6}” 

I have already tried using a when statement and a try and catch of SQL. Here it is a sql fiddle to show my problem http://sqlfiddle.com/#!17/7073d/2

Answer 1

TO_JSON converts Postgres type to JSON representation:

Returns the value as JSON. Arrays and composites are converted (recursively) to arrays and objects; otherwise, if there is a cast from the type to json, the cast function will be used to perform the conversion; otherwise, a JSON scalar value is produced. For any scalar type other than a number, a Boolean, or a null value, the text representation will be used, properly quoted and escaped so that it is a valid JSON string.

Thus, your string are converted to JSON strings. You should use a cast instead, and you can do the conversion and table modification in just one statement:

ALTER TABLE data_copy1 ALTER data TYPE JSONB USING data::JSONB

Answer 2

ALTER TABLE data_copy1 ADD COLUMN data2 jsonb;
UPDATE data_copy1 set data2= to_json(data) where LEFT(data, 1)= 'p' or LEFT(data, 1)= '{';
UPDATE data_copy1 set data2= to_json(data::numeric) where LEFT(data, 1)!= 'p' and LEFT(data, 1)!= '{';
ALTER TABLE data_copy1 DROP COLUMN data;
ALTER TABLE data_copy1 RENAME COLUMN data2 TO data;

I find out that with this code my problem is solved, but it will only work on our db by the way it is programed, i'm open to read other solutions :)