Invalid token error when using jsonb_insert in postgresql-CodePudding

As a little bit of background. I want to fill a column with jsonb values using values from other columns. Initially, I used this query:

UPDATE myTable
SET column_name = 
 row_to_json(rowset)
 FROM (SELECT column1, column2 FROM myTable)rowset

However, this query seems to run for way too long (a few hours before I stopped it) on a dataset with 9 million records. So I looking for a solution with the second FROM clause and found the jsonb_insert function. To test the query I first ran this sample query:

SELECT jsonb_insert('{}','{column1}','500000')

Which gives {'column1':500000} as output. Perfect, so I tried to fill the value using the actual column:

SELECT jsonb_insert('{}':,'{column1}',column1) FROM myTable WHERE id = <test_id>

This gives a syntax error and a suggestion to add argument types, which leads me to the following:

SELECT jsonb_insert('{}':,'{column1}','column1') 
FROM myTable WHERE id = <test_id>

SELECT jsonb_insert('{}'::jsonb,'{column1}'::jsonb,column1::numeric(8,0)) 
FROM myTable WHERE id = <test_id>

Both these queries give invalid input type syntax error, with Token 'column1' is invalid.

I really can not seem to find the correct syntax for these queries using documentation. Does anyone know what the correct syntax would be?

CodePudding user response：

Because jsonb_insert function might need to use jsonb type for the new_value parameter

jsonb_insert(target jsonb, path text[], new_value jsonb [, insert_after boolean])

if we want to get number type of JSON, we can try to cast the column as string type before cast jsonb

if we want to get a string type of JSON, we can try to use concat function with double-quotes sign.

CREATE TABLE myTable (column1 varchar(50),column2 int);

INSERT INTO myTable  VALUES('column1',50000);

SELECT jsonb_insert('{}','{column1}',concat('"',column1,'"')::jsonb) as JsonStringType,
       jsonb_insert('{}','{column2}',column2::TEXT::jsonb) as JsonNumberType
FROM myTable

sqlfiddle