sql - 从没有唯一列的表中删除部分重复项

Question

我必须从没有主键或唯一约束的表中清除记录。

表定义:

create table person(
    name text,
    staff_id integer,
    work_code text,
    location
);

不出所料，它包含大量重复项和部分重复项。将记录转换为唯一集的最佳方法是什么。除了 name 和 staff_id 之外，我不需要关心其他列

Answer 1

像你一样

don't have to care about other columns besides name and staff_id

这可能是您清理表格的过程:

1.) 创建一个唯一行的临时表:

CREATE TEMP TABLE p_tmp AS
SELECT DISTINCT ON (name, staff_id)
       name, staff_id, work_code, location
FROM   person
ORDER  BY name, staff_id, work_code, location;

我任意选择“每个 (name, staff_id) 的第一行 - 最小 work_code 和匹配的 location。

2.) 空表:

TRUNCATE person;

3.) 重新插入独特的元组:

INSERT INTO person SELECT * FROM p_tmp;

确保，骗子不会偷偷回来。添加代理主键:

ALTER TABLE person ADD COLUMN person_id serial PRIMARY KEY;
ALTER TABLE person ADD UNIQUE (name, staff_id);

或者只添加一个多列主键:

ALTER TABLE person ADD PRIMARY KEY (name, staff_id);

临时表将在 session 结束时自动删除。

当然，所有这些最好在一个transaction 内完成，因此在不太可能的情况下，您不会在中途遇到问题时丢失任何东西。对于同时执行的一批 SQL 语句，一些客户端会自动执行此操作。