sql - 将列从 varchar 更新为 jsonb

Question

我正在尝试将一个列从 varchar 类型的列更新为 JSON，但该列已经填充了数字、字符串或散列的字符。

接下来如何使用 TO_JSON 将它们转换为 JSON？

我尝试过的

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;

返回结果:

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

数值也被视为字符串，我希望它们变成integer、float 等。我希望同一列是这样的:

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

我已经尝试过使用 when 语句和 SQL 的 try and catch。这是一个 sql fiddle 来显示我的问题 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.

因此，您的字符串被转换为 JSON 字符串。您应该改用强制转换，只需一条语句即可完成转换和表修改:

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