添加唯一约束但忽略现有表数据。

Question

我希望使用以下查询向表中添加唯一约束条件：

ALTER TABLE events ADD CONSTRAINT events_timestamp_request_session_key UNIQUE (event_timestamp, request_id, session_id);

查询是有效的，但是在一个数据库中，现有的数据不符合约束条件，因此查询失败 -

错误：无法创建唯一索引“events_timestamp_request_session_key” 详细信息：键（event_timestamp，request_id，session_id）=（2017-07-05 14:53:25.475246+00，a55df6-8533e322cd-aa9d57-87e2，132443）重复。

预计会有一些重复项，但不幸的是我不能简单地删除或更改它们。

是否有任何方法可以按要求添加约束条件，同时忽略表中现有的数据？

- David Gard

Answer 1

您可以使用部分索引来解决这个问题，虽然这不是一个特别好的解决方案，但在您能够更正旧数据之前，它将起到作用。

例如：

CREATE UNIQUE INDEX events_timestamp_request_session_key
ON events (event_timestamp, request_id, session_id)
WHERE event_timestamp >= '2017-07-01'::timestamp;

时间是您的清洁数据开始的时间。

where子句将索引限制为仅查看具有较新事件时间戳的记录。旧记录完全被排除在索引之外，因此不被考虑用于唯一性检查。

文档：https://www.postgresql.org/docs/9.6/static/indexes-partial.html

- Gary - Stand with Ukraine

---

它有效并强制使这些列的组合唯一，所以谢谢你。然而，从现在开始，你知道查询的影响吗？即每周添加大约3百万行，查询是否比具有唯一约束的查询慢？谢谢。 - David Gard

@David Gard。插入速度会变慢，因为它们需要维护额外的索引。您需要进行基准测试以确定是否会造成问题。需要考虑的一件事是，因为这是一个部分索引，它不会帮助您运行可能查看旧数据的任何查询，因此您可能需要2个索引，一个用于部分表的唯一索引，另一个用于整个表的非唯一索引。 - Gary - Stand with Ukraine

Answer 2

我认为没有内置的方法可以实现这个功能。不过，你可以使用表达式创建一个唯一索引。假设每个表中都有一个序列唯一标识：

create unique index unq_events_timestamp_request_session_key 
    on (event_timestamp, request_id, session_id,
        (case when event_id < 99999 then event_id else -1 end)
       ); 

这个表达式的意思是：“如果这个键已经在表中存在，则为唯一约束忽略它”。通过消除当前的重复项，你可以强制实施所有唯一约束，但是不包括它们。

create unique index unq_events_timestamp_request_session_key 
    on (event_timestamp, request_id, session_id,
        (case when event_id in (1, 2, 3, 5, 8) then event_id
              else -1
         end)
       ); 

需要调查当前的重复项。实际上，您也可以使用筛选子句来执行以下操作：

create unique index unq_events_timestamp_request_session_key 
    on (event_timestamp, request_id, session_id)
    where event_id > 999999 ;

create unique index unq_events_timestamp_request_session_key 
    on (event_timestamp, request_id, session_id)
    where event_id not in (1, 2, 3, 5, 8) ;

- Gordon Linoff