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

我对这个基本但重要的问题有些怀疑，所以我决定以身作则。

让我们创建带有两个列的测试表master，带有唯一约束的con_id和带有唯一索引的ind_id。

create table master (
    con_id integer unique,
    ind_id integer
);
create unique index master_unique_idx on master (ind_id);

    Table "public.master"
 Column |  Type   | Modifiers
--------+---------+-----------
 con_id | integer |
 ind_id | integer |
Indexes:
    "master_con_id_key" UNIQUE CONSTRAINT, btree (con_id)
    "master_unique_idx" UNIQUE, btree (ind_id)

在表描述(psql中的\d)中，你可以区分唯一的约束和唯一的索引。

独特性

以防万一，我们来检查一下唯一性。

test=# insert into master values (0, 0);
INSERT 0 1
test=# insert into master values (0, 1);
ERROR:  duplicate key value violates unique constraint "master_con_id_key"
DETAIL:  Key (con_id)=(0) already exists.
test=# insert into master values (1, 0);
ERROR:  duplicate key value violates unique constraint "master_unique_idx"
DETAIL:  Key (ind_id)=(0) already exists.
test=#

它像预期的那样工作!

外键

现在，我们将用两个外键来定义详细表，引用master中的两列。

create table detail (
    con_id integer,
    ind_id integer,
    constraint detail_fk1 foreign key (con_id) references master(con_id),
    constraint detail_fk2 foreign key (ind_id) references master(ind_id)
);

    Table "public.detail"
 Column |  Type   | Modifiers
--------+---------+-----------
 con_id | integer |
 ind_id | integer |
Foreign-key constraints:
    "detail_fk1" FOREIGN KEY (con_id) REFERENCES master(con_id)
    "detail_fk2" FOREIGN KEY (ind_id) REFERENCES master(ind_id)

没有错误。让我们确保它能工作。

test=# insert into detail values (0, 0);
INSERT 0 1
test=# insert into detail values (1, 0);
ERROR:  insert or update on table "detail" violates foreign key constraint "detail_fk1"
DETAIL:  Key (con_id)=(1) is not present in table "master".
test=# insert into detail values (0, 1);
ERROR:  insert or update on table "detail" violates foreign key constraint "detail_fk2"
DETAIL:  Key (ind_id)=(1) is not present in table "master".
test=#

两个列都可以在外键中引用。

使用索引约束

可以使用现有的唯一索引添加表约束。

alter table master add constraint master_ind_id_key unique using index master_unique_idx;

    Table "public.master"
 Column |  Type   | Modifiers
--------+---------+-----------
 con_id | integer |
 ind_id | integer |
Indexes:
    "master_con_id_key" UNIQUE CONSTRAINT, btree (con_id)
    "master_ind_id_key" UNIQUE CONSTRAINT, btree (ind_id)
Referenced by:
    TABLE "detail" CONSTRAINT "detail_fk1" FOREIGN KEY (con_id) REFERENCES master(con_id)
    TABLE "detail" CONSTRAINT "detail_fk2" FOREIGN KEY (ind_id) REFERENCES master(ind_id)

现在列约束描述没有区别了。

部分指标

在表约束声明中不能创建部分索引。 它直接来自创建表....的定义 在唯一索引声明中，可以设置WHERE子句来创建部分索引。 您还可以在表达式上创建索引(不仅仅是在列上)，并定义一些其他参数(排序规则、排序顺序、空值位置)。

不能使用部分索引添加表约束。

alter table master add column part_id integer;
create unique index master_partial_idx on master (part_id) where part_id is not null;

alter table master add constraint master_part_id_key unique using index master_partial_idx;
ERROR:  "master_partial_idx" is a partial index
LINE 1: alter table master add constraint master_part_id_key unique ...
                               ^
DETAIL:  Cannot create a primary key or unique constraint using such an index.

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

所以，这是行不通的:

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

但下面的做法是可行的。

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

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

我在医生里读到:

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为你添加一个唯一索引的实现。

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