PostgreSQL：基于同名jsonb字段设置表列值

你想要的更新操作完全可以通过PostgreSQL的jsonb字段提取语法实现，你写的基础语句方向是对的，直接执行就能完成同步：

UPDATE project 
SET col1 = report->>'col1', 
    col2 = report->>'col2', 
    col3 = report->>'col3';

几个实用的优化和注意事项 #

• 处理缺失键或空值：如果report字段里可能不存在某个键，或者对应的值是null，可以用COALESCE给目标字段设置默认值，避免更新后出现空值：

  UPDATE project 
  SET col1 = COALESCE(report->>'col1', '未填写'), 
      col2 = COALESCE(report->>'col2', '0') -- 针对数字类型字段用数字默认值
  

• 只更新有变化的行：如果表数据量较大，不想做无意义的重复更新，可以加个WHERE条件过滤掉值完全一致的行，提升性能：

  UPDATE project 
  SET col1 = report->>'col1', 
      col2 = report->>'col2', 
      col3 = report->>'col3'
  WHERE col1 IS DISTINCT FROM report->>'col1'
     OR col2 IS DISTINCT FROM report->>'col2'
     OR col3 IS DISTINCT FROM report->>'col3';
  

• 提前验证数据：执行UPDATE前可以先跑个SELECT语句确认提取的值是否符合预期，避免误操作：

  SELECT col1 AS 原col1值, report->>'col1' AS 待更新col1值,
         col2 AS 原col2值, report->>'col2' AS 待更新col2值
  FROM project
  LIMIT 10;
  

内容的提问来源于stack exchange，提问作者Bargain23