检查Postgres表中列的唯一性。

Question

作为更大过程的一部分，我需要确保表中某列的值是唯一的。

我知道可以使用UNIQUE约束来实现，但我想知道是否有更好的方法来进行检查。

我正在使用psycopg2运行查询，所以在这里添加该标签以寻找可能有助于此问题的内容。

如果该列是唯一的，那么我可以添加一个约束。如果该列不是唯一的，则添加约束将返回错误。

如果已经存在同名的约束，则会返回一个有用的错误。在这种情况下，最好只检查现有的约束。

如果该列是主键，则可以添加唯一约束而不出错，但在这种情况下，最好根据主键识别该列必须是唯一的。

以下是相关代码示例。

DROP TABLE IF EXISTS unique_test;
CREATE TABLE unique_test (
pkey INT PRIMARY KEY,
unique_yes CHAR(1),
unique_no CHAR(1)
);

INSERT INTO unique_test (pkey, unique_yes, unique_no)
VALUES(1, 'a', 'a'),
        (2, 'b', 'a');
    
CREATE UNIQUE INDEX CONCURRENTLY u_test_1  ON unique_test (unique_yes);

ALTER TABLE unique_test
ADD CONSTRAINT unique_target_1
UNIQUE USING INDEX u_test_1;

-- the above runs no problem

-- check what happens when column is not unique

CREATE UNIQUE INDEX CONCURRENTLY u_test_2  ON unique_test (unique_no);

ALTER TABLE unique_test
ADD CONSTRAINT unique_target_2
UNIQUE USING INDEX u_test_2;

-- returns:
-- SQL Error [23505]: ERROR: could not create unique index "u_test_2"
--  Detail: Key (unique_no)=(a) is duplicated.


CREATE UNIQUE INDEX CONCURRENTLY u_test_1  ON unique_test (unique_yes);

ALTER TABLE unique_test
ADD CONSTRAINT unique_target_1
UNIQUE USING INDEX u_test_1;

-- returns
-- SQL Error [42P07]: ERROR: relation "unique_target_1" already exists


-- test what happens if adding constrint to primary key column

CREATE UNIQUE INDEX CONCURRENTLY u_test_pkey  ON unique_test (pkey);

ALTER TABLE unique_test
ADD CONSTRAINT unique_target_pkey
UNIQUE USING INDEX u_test_pkey;

-- this runs no problem but is inefficient.

- Hugh_Kelley

---

我有点不清楚你指的是哪一列以及你想要什么结果。 - Gordon Linoff

Answer 1

如果你只想验证值是否唯一，那么使用查询：

select unique_no, count(*)
  from unique_test
 group by unique_no
having count(*) > 1;

如果需要布尔类型的输出：

select not exists (
  select unique_no, count(*)
    from unique_test
   group by unique_no
  having count(*) > 1
);

- Mike Organek

---

显然，谢谢，只是变得太花哨了，超出了我的能力范围。 - Hugh_Kelley

如果您的进程进行了足够多的这些检查以致于使其变得不可接受地缓慢，那么您可以查看 information_schema.constraint_column_usage 和 information_schema.table_constraints 来绕过此类查询。 - Mike Organek

快速问题；由于您使用了“不存在”，“True”输出意味着没有重复项，对吗？ - GeorgeWTrump

Answer 2

如果您只需要一个标记，您可以使用：

select count(*) <> count(distinct uniq_no) as duplicate_flag
from unique_test;

- Gordon Linoff

Answer 3

DELETE FROM
zoo x
USING zoo y
WHERE
x.animal_id < y.animal_id
AND x.animal = y.animal;

我认为这个更简单，点击此处参考。

- Eshwar Bychanahalli

---

这是一个有帮助的查询，但我不能仅仅将重复行丢弃，因为它可能是一个意图中的主键问题，而不是真正的重复。 - Hugh_Kelley