sql - 在已插入非唯一值后在 PostgreSQL 表列上强制执行唯一性

Question

我已经有 8000 万条记录插入到一​​个表中，但需要确保一些列是联合唯一的。但是，列已经包含非唯一数据，因此 ALTER TABLE 不起作用。

我想要一个可以让我轻松删除非唯一记录同时保留其中一个记录的查询，或者一个可以让我将数据从当前表加载到新表中同时进行过滤的查询为了唯一性。

Answer 1

您要查找的查询是:

select distinct on (my_unique_1, my_unique_2) * from my_table;

这将为 distinct on 中的每个列组合选择一行。实际上，它总是第一 行。它很少在没有 order by 的情况下使用，因为没有可靠的行返回顺序(所以哪一个是第一行)。

结合 order by，您可以选择排在第一位的行(这会留下具有最​​大 last_update_date 的行):

 select distinct on (my_unique_1, my_unique_2) * 
 from my_table order by my_unique_1, my_unique_2, last_update_date desc;

现在您可以将其选择到一个新表中:

 create table my_new_table as
 select distinct on (my_unique_1, my_unique_2) * 
 from my_table order by my_unique_1, my_unique_2, last_update_date desc;

或者你可以用它来删除，假设 row_id 是一个主键:

 delete from my_table where row_id not in (
     select distinct on (my_unique_1, my_unique_2) row_id 
     from my_table order by my_unique_1, my_unique_2, last_update_date desc);