如何清理posts_media表重复行？保留关联最早posts记录的条目

我有两张数据表：

posts表结构 #

id, -- 唯一主键 bigserial
post_id, -- integer
[...] -- 其他列

posts_media表结构 #

id, -- 唯一主键 bigserial
post_db_id, -- bigint，关联`posts`表的`id`字段
post_id, -- integer，与关联的`posts`表行中post_id值一致
[...] -- 其他列

爬虫采集的数据源会重复展示同一篇帖子，导致数据库中出现重复的posts记录。我计划为posts表的post_id字段添加UNIQUE约束避免后续重复，清理posts表的重复行比较简单，但需要同时清理posts_media表中的重复数据。

一个post可以对应多条posts_media记录，我的思路是：筛选posts_media中post_id重复的行，按post_db_id分组后，仅保留post_db_id最小（对应最早posts记录）的那些行。这个思路是否正确？具体该如何实现？我希望保留创建时间最早（id或post_db_id最小）的行。

示例posts_media数据 #

针对该示例，希望保留id为1、2、7、8的行，删除其余行。

思路验证 #

你的思路完全正确。对于同一post_id的重复帖子，保留最早创建（id最小）的posts记录，同时保留该记录对应的所有posts_media行，删除其他重复post_id对应的posts_media数据，逻辑自洽且符合需求。

具体实现步骤 #

1. 清理posts表的重复行 #

先确定每个post_id对应的最小id（最早记录），保留这些行并删除其他重复行：

-- 创建临时表存储需要保留的posts记录ID
CREATE TEMP TABLE keep_posts AS
SELECT MIN(id) AS keep_id
FROM posts
GROUP BY post_id;

-- 删除posts表中不在保留列表的重复行
DELETE FROM posts
WHERE id NOT IN (SELECT keep_id FROM keep_posts);

2. 清理posts_media表的重复数据 #

找出每个post_id对应的最小post_db_id（对应posts表中保留的最早记录），保留这些post_db_id对应的所有posts_media行：

-- 方式一：用临时表实现
CREATE TEMP TABLE keep_media_post_db_ids AS
SELECT MIN(post_db_id) AS keep_post_db_id
FROM posts_media
GROUP BY post_id;

DELETE FROM posts_media
WHERE post_db_id NOT IN (SELECT keep_post_db_id FROM keep_media_post_db_ids);

或者直接用关联查询，无需临时表：

-- 方式二：直接关联删除
DELETE FROM posts_media pm
WHERE pm.post_db_id NOT IN (
    SELECT MIN(pm_inner.post_db_id)
    FROM posts_media pm_inner
    WHERE pm_inner.post_id = pm.post_id
    GROUP BY pm_inner.post_id
);

3. 添加UNIQUE约束防止后续重复 #

清理完成后，为posts表的post_id字段添加唯一约束：

ALTER TABLE posts
ADD CONSTRAINT unique_post_id UNIQUE (post_id);

补充说明 #

• 操作前建议备份数据，避免误删；
• 若数据量较大，临时表的方式执行效率更高；
• 可额外清理posts_media表中已失效的关联（即post_db_id在posts表中不存在的行）：

DELETE FROM posts_media
WHERE post_db_id NOT IN (SELECT id FROM posts);

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