将EAV表转换为JSON/B并生成PostgreSQL更新迁移查询

没问题，我刚好处理过类似的EAV结构转JSON的迁移需求，针对你的PostgreSQL 10.3版本，给你一套实用的解决方案：

第一步：确保Record表已新增JSONB列 #

首先得确认你的Record表已经添加了用来存储JSON数据的列，推荐用jsonb类型（比原生json更适合后续的查询和修改操作，性能更优）。如果还没加，先执行这条SQL：

ALTER TABLE Record ADD COLUMN metadata jsonb DEFAULT '{}'::jsonb;

DEFAULT '{}'::jsonb是为了给没有对应RecInfos记录的行默认一个空JSON对象，避免出现null值。

第二步：核心更新查询 #

接下来就是把RecInfos里的key-value对聚合为JSONB，批量更新到Record表的新列中。用jsonb_object_agg函数可以直接将分组后的key和value聚合成JSONB对象，配合UPDATE ... FROM语法关联两张表：

UPDATE Record r
SET metadata = COALESCE(ri.metadata_json, '{}'::jsonb)
FROM (
    SELECT 
        recordid,
        jsonb_object_agg(key, value) AS metadata_json
    FROM RecInfos
    GROUP BY recordid
) ri
WHERE r.id = ri.recordid;

代码细节解释： #

• 子查询ri会按recordid分组，把每个Record对应的所有key和value聚合成一个JSONB对象
• COALESCE用来兜底：如果某个Record没有任何RecInfos记录，确保它的metadata列是空对象而不是null
• UPDATE ... FROM是PostgreSQL中关联其他表做批量更新的常用写法，逻辑清晰且高效

实用注意事项 #

• 先测试再执行：如果是生产环境，建议先在测试环境验证逻辑，或者先用SELECT预览更新结果：

  SELECT r.id, COALESCE(ri.metadata_json, '{}'::jsonb) AS new_metadata
  FROM Record r
  LEFT JOIN (
      SELECT recordid, jsonb_object_agg(key, value) AS metadata_json
      FROM RecInfos
      GROUP BY recordid
  ) ri ON r.id = ri.recordid;
  

• 大数据量分批处理：如果你的Record表数据量很大（比如几十万行以上），直接全表更新可能锁表太久，建议按id范围分段更新：

  UPDATE Record r
  SET metadata = COALESCE(ri.metadata_json, '{}'::jsonb)
  FROM (...) ri
  WHERE r.id = ri.recordid AND r.id BETWEEN 1 AND 1000;
  

  循环执行直到所有行更新完成。
• 后续查询优化：如果之后需要基于metadata列的内容做查询，可以给它创建GIN索引，大幅提升查询速度：

  CREATE INDEX idx_record_metadata ON Record USING GIN (metadata);
  

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