我在医生里读到:

ADD table_constraint [ NOT VALID ] This form adds a new constraint to a table using the same syntax as CREATE TABLE, plus the option NOT VALID, which is currently only allowed for foreign key constraints. If the constraint is marked NOT VALID, the potentially-lengthy initial check to verify that all rows in the table satisfy the constraint is skipped. The constraint will still be enforced against subsequent inserts or updates (that is, they'll fail unless there is a matching row in the referenced table). But the database will not assume that the constraint holds for all rows in the table, until it is validated by using the VALIDATE CONSTRAINT option.

所以我认为这就是你所说的“部分唯一性”通过添加一个约束。

以及，关于如何确保唯一性:

Adding a unique constraint will automatically create a unique B-tree index on the column or group of columns listed in the constraint. A uniqueness restriction covering only some rows cannot be written as a unique constraint, but it is possible to enforce such a restriction by creating a unique partial index. Note: The preferred way to add a unique constraint to a table is ALTER TABLE … ADD CONSTRAINT. The use of indexes to enforce unique constraints could be considered an implementation detail that should not be accessed directly. One should, however, be aware that there’s no need to manually create indexes on unique columns; doing so would just duplicate the automatically-created index.

因此，我们应该添加约束，它创建一个索引，以确保唯一性。

我怎么看这个问题?

“约束”的目的是在语法上确保这一列应该是唯一的，它建立了一个法律，一个规则;而“索引”是语义上的，关于“如何实现，如何实现唯一性，在实现时唯一性意味着什么”。所以，Postgresql实现它的方式是非常符合逻辑的:首先，你声明一个列应该是唯一的，然后，Postgresql为你添加一个唯一索引的实现。

唯一性是一个限制。它恰好是通过创建来实现的 唯一的索引，因为一个索引能够很快地搜索所有现有的 值，以确定给定值是否已经存在。 从概念上讲，索引是一个实现细节，惟一性也应该是 只与约束相关联。

全文

所以速度性能应该是一样的

由于许多人已经提供了唯一索引相对于唯一约束的优点，这里有一个缺点:唯一约束可以延迟(仅在事务结束时检查)，唯一索引不能。

我遇到的另一件事是，您可以在唯一索引中使用sql表达式，但不能在约束中使用。

所以，这是行不通的:

CREATE TABLE users (
    name text,
    UNIQUE (lower(name))
);

但下面的做法是可行的。

CREATE TABLE users (
    name text
);
CREATE UNIQUE INDEX uq_name on users (lower(name));

SELECT a.phone_number,count(*) FROM public.users a
Group BY phone_number Having count(*)>1;

SELECT a.phone_number,count(*) FROM public.retailers a
Group BY phone_number Having count(*)>1;

select a.phone_number from users a inner join users b
on a.id <> b.id and a.phone_number = b.phone_number order by a.id;


select a.phone_number from retailers a inner join retailers b
on a.id <> b.id and a.phone_number = b.phone_number order by a.id

DELETE FROM
    users a
        USING users b
WHERE
    a.id > b.id
    AND a.phone_number = b.phone_number;
    
DELETE FROM
    retailers a
        USING retailers b
WHERE
    a.id > b.id
    AND a.phone_number = b.phone_number;
    
CREATE UNIQUE INDEX CONCURRENTLY users_phone_number 
ON users (phone_number);

验证:

insert into users(name,phone_number,created_at,updated_at) select name,phone_number,created_at,updated_at from users

使用UNIQUE INDEX与UNIQUE CONSTRAINT的另一个优点是，您可以轻松地并发地删除/创建索引，而使用CONSTRAINT则不行。