PostgreSQL – 如何消除重复值

有可能在表中，某些具有重复值的字段需要使其保持唯一。
以及如何处理重复的值而不将它们全部消除？
是否可以只保留最新的？

ctid系统列
每个表都有一些系统隐式定义的列，它们的名称是保留的。
目前系统列有：tableoid、xmin、cmin、xmax、cmax和ctid。每个都有来自它们所属表的元数据。
ctid 系统列旨在存储行的物理位置的版本。如果行
更新 (UPDATE) 或表经历 VACUUM FULL，则此版本可能会更改。ctid
的数据类型是 tid，表示元组标识符（或行标识符），它是一对（块号，块内的元组索引）
，用于标识行在表中的物理位置。
此列在表中始终具有其唯一值，因此当存在具有重复值的行时，可以将其用作消除它们的标准。

测试表创建：

CREATE TABLE tb_test_ctid (
    col1 int,
    col2 text);


复制

插入一些数据：

INSERT INTO tb_test_ctid VALUES 
(1, 'foo'),
(2, 'bar'),
(3, 'baz');

复制

检查当前行：

SELECT ctid, * FROM tb_test_ctid;

复制

更新一行：

UPDATE tb_test_ctid SET col2 = 'spam' WHERE col1 = 1;

UPDATE tb_test_ctid SET col2 = 'eggs' WHERE col1 = 1 RETURNING ctid;

复制

再次检查表：

SELECT ctid, * FROM tb_test_ctid;

复制

使用 ctid 消除重复值
想象一下，一个表在一个字段中具有重复的值，并且该字段稍后被决定使其唯一。
请记住，PRIMARY KEY 字段也是唯一的。
好的，决定删除该字段中的重复值。
现在有必要建立一个标准来决定这些重复值将保留。
在以下情况下，标准是最新的行，即具有最高 ctid 值的行。

New test table creation:

CREATE TABLE tb_foo(
    id_ int,  --This field will be the primary key in the future!
    letter char(1)
);
复制

插入一些数据

INSERT INTO tb_foo (id_, letter) SELECT generate_series(1, 10), 'a';

复制

查表：

SELECT id_, letter FROM tb_foo;

复制

插入一些数据

INSERT INTO tb_foo (id_, letter) SELECT generate_series(1, 3), 'b';
复制

找出重复值：

WITH t AS (
SELECT
    id_,
    count(id_) OVER (PARTITION BY id_) AS count_id,  -- Count
    ctid,
    max(ctid) OVER (PARTITION BY id_) AS max_ctid  -- Most current ctid
    
    FROM tb_foo
)

SELECT
    t.id_,
    t.max_ctid
    FROM t
    WHERE t.count_id > 1  -- Filters which values repeat
    GROUP by id_, max_ctid;

复制

WITH

t1 AS (
SELECT
    id_,
    count(id_) OVER (PARTITION BY id_) AS count_id,
    ctid,
    max(ctid) OVER (PARTITION BY id_) AS max_ctid
    
    FROM tb_foo
),

t2 AS (  -- Virtual table that filters repeated values that will remain
SELECT t1.id_, t1.max_ctid
    FROM t1
    WHERE t1.count_id > 1
    GROUP by t1.id_, t1.max_ctid)

DELETE  -- DELETE with JOIN 
    FROM tb_foo AS f
    USING t2
    WHERE 
        f.id_ = t2.id_ AND  -- tb_foo has id_ equal to t2 (repeated values)
        f.ctid < t2.max_ctid;  -- ctid is less than the maximum (most current)

复制

加上主键

ALTER TABLE tb_foo ADD CONSTRAINT tb_foo_pkey PRIMARY KEY (id_);

复制