记录一下PostgreSQL的拓展字段(JsonB字段)的update写法

/*不论做什么UPDATE操作，一定要备份*/

样例1：直接根据值更新 

UPDATE
    tablename 
SET
    ext = (ext||('{"id" :"12345678"}')::jsonb)

样例2：根据中间表更新

【此步骤一定要小心，做错了会把整个ext字段给清空掉】

WITH a AS(

SELECT
　　, uid
　　, productid pid
　　, standard_rate rate
   , year_sales
   , year_plan_sale
FROM 
    tablename1 tb

)
UPDATE 
    tablename2 tn
SET
　　   product = a.pid
      ,ext = ext||(('{
        "rate" :"'||a.rate||'","year_sales" :"'||a.year_sales||'","year_plan_sale" :"'||a.year_plan_sale||'"
      }')::jsonb)
FROM 
    a
WHERE 
    a.uid = tn.id