postgresql - 如何展平也在关系中使用的区分大小写的列

Question

您好 postgres 专家，

我有一个 users表格

<表类="s-表"><头>id电子邮件<正文>1john@example.com2John@example.com

还有一个 posts表格

<表类="s-表"><头>id用户ID内容<正文>11富22栏

两个用户的电子邮件在忽略大小写时是相同的，所以我正在寻找一种方法来删除具有重复电子邮件的行并更新 userId在posts表指向剩余的用户。

所以最后的结果会是:

<表类="s-表"><头>id电子邮件<正文>1john@example.com

<表类="s-表"><头>id用户ID内容<正文>11富21栏

我不关心我最终使用哪个版本的电子邮件地址(即不必是全部小写的那个)。

完成这项工作的最佳方法是什么？

Answer 1

您可以通过取最小的 id 来更新 posts 表:

update posts p
  set userid = u.user_id
from (
  select min(id) user_id, array_agg(id) as user_ids
  from users u
  group by lower(email)
  having count(*) > 1
) u
where p.userid = any(u.user_ids)
  and p.userid <> u.user_id
;

派生表中的 SELECT 返回所有拥有多个电子邮件地址的用户。 WHERE 子句然后更新 posts 表以使用其中一个 ID。完成后，您可以删除不再使用的用户

delete from users
where not exists (select * 
                  from posts p 
                  where users.id = p.userid);

Online example