PostgreSQL Update the JSONB column using another list

I want to migrate data from one column (varchar) to another (jsonb)

Column | Type | Modifiers < br />------------+-----------------------------+---- -------------------------------------------------- --
id | integer | not null default nextval('merchants_id_seq'::regclass)
name | character varying | not null
nameb | jsonb | not null default'{}':: jsonb

So nameb will become {“en”:”$name”}, where $name is the value in the name field.

For example:

SELECT name,nameb

Before:

name | nameb 
----------------- ---------------------+------------
hello | {}
world | {}

After:

name | nameb 
-------------------- ------------------+------------
hello | {"en": "hello"}
world | {"en": "world"}

Using the regualar type I can do UPDATE SET whatever =(SELECT …), but how to use it Does jsonb do this?

UPDATE Merchant SET nameb =(SELECT'{“en”:”fillme!”}’:: jsonb);It works, but how to set the value of “fillme!” in another field?

I found a solution

< pre>UPDATE merchants AS m1
SET nameb = (
SELECT row_to_json(t) FROM (
SELECT name as en FROM merchants AS m2 WHERE m1.id = m2.id
) t
)::jsonb;

Not sure if it is correct, but it does work

I want to put the data from one column (varchar ) Migrate to another column (jsonb)

Column | Type | Modifiers 
------------+--- --------------------------+----------------------- ---------------------------------
id | integer | not null default nextval('merchants_id_seq': :regclass)
name | character varying | not null
nameb | jsonb | not null default'{}'::jsonb

So nameb will become {“en”: “$ name”}, where $name is the value in the name field.

For example:

SELECT name,nameb

Before:

name | nameb 
-------------------------------- ------+------------
hello | {}
world | {}

After:

name | nameb 
----------------------------------- ---+------------
hello | {"en": "hello"}
world | {"en": "world"}

Using regualar type I can do UPDATE SET whatever =(SELECT …), but how to do it with jsonb?

UPDATE Merchant SET nameb =(SELECT'{“en”:”fillme!”}’:: jsonb);It works, but how to set the value of “fillme!” in another field?

I found a solution

UPDATE merchants AS m1 
SET nameb = (
SELECT row_to_json(t) FROM (
SELECT name as en FROM merchants AS m2 WHERE m1.id = m2.id
) t
)::jsonb;

Not sure if it is correct, but it does work

Leave a Comment

Your email address will not be published.