交叉连接目标表无匹配字段时如何引用jsonb表中的updated字段

开发者阿泽

2026-10-3

你可以直接通过 PostgreSQL 内置的 jsonb 运算符?判断data字段顶层是否存在updated键，不需要将updated字段映射到目标表即可完成分类处理，以下是具体实现方案：

方案1：分开执行新增、更新逻辑

适合两类数据需要单独控制执行时机的场景：

处理无updated字段的新增记录

INSERT INTO musicbrainz.acoustid_track (id, created, gid, new_id)
SELECT t.id, t.created, t.gid, t.new_id
FROM musicbrainz.acoustid_track_json j
CROSS JOIN jsonb_populate_record(null::musicbrainz.acoustid_track, j.data) t
WHERE NOT j.data ? 'updated';

处理有updated字段的更新记录（假设id为acoustid_track表的主键）

UPDATE musicbrainz.acoustid_track t
SET 
  gid = jt.gid,
  created = jt.created,
  new_id = COALESCE(jt.new_id, t.new_id)
FROM musicbrainz.acoustid_track_json j
CROSS JOIN jsonb_populate_record(null::musicbrainz.acoustid_track, j.data) jt
WHERE j.data ? 'updated' AND t.id = jt.id;

方案2：单条UPSERT语句批量处理

适合需要一次性执行所有数据同步的场景，利用PostgreSQL的INSERT ... ON CONFLICT特性自动处理新增/更新：

INSERT INTO musicbrainz.acoustid_track (id, created, gid, new_id)
SELECT t.id, t.created, t.gid, t.new_id
FROM musicbrainz.acoustid_track_json j
CROSS JOIN jsonb_populate_record(null::musicbrainz.acoustid_track, j.data) t
ON CONFLICT (id) DO UPDATE 
SET 
  gid = EXCLUDED.gid,
  created = EXCLUDED.created,
  new_id = EXCLUDED.new_id
-- 仅当源JSON存在updated字段时才执行冲突更新，避免新增数据意外覆盖旧记录
WHERE j.data ? 'updated';