sql - 在 Postgres 的单个索引中包含多个列

Question

我有一个包含两列的“用户”表，“电子邮件”和“新电子邮件”。我需要:

• 覆盖两列的不区分大小写的唯一性约束 - 即，如果“Bob@Example.com”出现在一行的“电子邮件”列中，则将“bob@example.com”插入另一行(甚至同一行的)“new_email”列应该失败。

• 在“电子邮件”或“new_email”字段中快速搜索不区分大小写的给定电子邮件地址 - 即查找 new_email 或电子邮件为“Bob@example.com”的行，不区分大小写。

我知道我可以通过创建一个相关的“电子邮件”表来更轻松地做到这一点，但我希望从几个应用程序中查找这个表中的用户(通过主键)，我想避免在不同的地方复制加入逻辑来检索他们的电子邮件。所以我认为如果可能的话，某种表达索引是最好的。

如果这不可能，我想我的下一个最佳选择是创建一个 View ，其他应用程序可以使用该 View 轻松获取用户的电子邮件以及他们的其他信息，但我不确定该怎么做要么。

我使用的是 Postgres 8.4。谢谢!

Answer 1

我认为您必须使用触发器来强制实现跨列唯一性约束。如果您在每一列上添加唯一索引，然后触发类似这样的东西(未经测试，我的代码就没有经过测试):

CREATE FUNCTION no_dups_allowed() RETURNS trigger AS $$
DECLARE
    r ROW;
BEGIN
    SELECT 1 INTO r
    FROM users
    WHERE LOWER(email)     = LOWER(NEW.email_new)
       OR LOWER(email_new) = LOWER(NEW.email);
    IF FOUND THEN
        -- Found a duplicate so it is time for a hissy fit!
        RAISE 'Duplicate email address found' USING ERRCODE = 'unique_violation';
    END;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

您需要类似的东西作为 BEFORE INSERT 和 BEFORE UPDATE 触发器。该触发器将负责捕获跨列重复项，而唯一索引将负责列内重复项。

一些有用的引用:

• FOUND
• RAISE
• Triggers
• Trigger Procedures

无论如何，您都需要为您的查询使用单独的索引，并且使用索引的唯一性一半可以简化您的触发器，让它只处理跨列部分；如果您尝试在触发器中完成这一切，那么您将不得不注意在没有真正更改 email 或 email_new 列的情况下更新行。

对于查询的一半，您可以 create a view使用了 UNION合并两列。您还可以创建一个函数来将用户的电子邮件地址合并到一个列表中。如果不知道这些其他查询的更多细节，很难说哪个最好，但我怀疑修复所有其他查询以了解 email 和 email_new 将是最好的方法；您必须更新所有其他查询才能使用 View 或函数，那么为什么要构建 View 或函数呢？