PostgreSQL中如何基于JSONB字段的id属性删除重复数据

没问题，我来帮你搞定这个需求～ 你的表是用data字段（JSONB类型）里的id值来判断重复，核心思路就是先找出每个id对应的目标保留记录（最新或最旧），再删除其他重复项。

方案1：保留最新的记录（created_at最大的那条） #

如果想保留每个id下创建时间最晚的记录，你可以用CTE（公共表表达式）先筛选出每个id的最大created_at，再删除那些不在这个范围内的重复项：

WITH latest_valid_records AS (
    -- 先找出每个data->>'id'对应的最新创建时间
    SELECT 
        MAX(created_at) AS latest_created,
        data->>'id' AS unique_id
    FROM your_table
    GROUP BY data->>'id'
)
-- 删除重复的旧记录
DELETE FROM your_table t
USING latest_valid_records lr
WHERE t.data->>'id' = lr.unique_id
  AND t.created_at < lr.latest_created;

你也可以用更简洁的子查询写法：

DELETE FROM your_table
WHERE (created_at, data->>'id') NOT IN (
    SELECT MAX(created_at), data->>'id'
    FROM your_table
    GROUP BY data->>'id'
);

方案2：保留最旧的记录（created_at最小的那条） #

要是你想保留每个id下最早创建的记录，只需要把上面的MAX换成MIN就行：

WITH earliest_valid_records AS (
    SELECT 
        MIN(created_at) AS earliest_created,
        data->>'id' AS unique_id
    FROM your_table
    GROUP BY data->>'id'
)
DELETE FROM your_table t
USING earliest_valid_records er
WHERE t.data->>'id' = er.unique_id
  AND t.created_at > er.earliest_created;

重要注意事项 #

• 操作前先验证：建议先把DELETE换成SELECT *，看看要删除的记录是不是你预期的，避免误删：

  -- 验证要删除的记录（以保留最新为例）
  SELECT * FROM your_table
  WHERE (created_at, data->>'id') NOT IN (
      SELECT MAX(created_at), data->>'id'
      FROM your_table
      GROUP BY data->>'id'
  );
  

• 备份数据：如果是生产环境，操作前一定要先备份表数据，比如用CREATE TABLE your_table_backup AS SELECT * FROM your_table;做个快照。

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