postgresql - Postgres 添加 seq# 以区分数据库中的唯一行

Question

在现有的应用程序中，我有一个没有主键的表，其中(很少)有重复的行。为了与另一个系统集成，我需要在表中添加一列，使这些重复的行唯一。该表本质上是:

+------+---------+--------+| txn# | detail# | amount |+------+---------+--------+

我可以只对每个现有行使用一个递增的 seq#，但是，应用程序更改将采用现有的“几乎关键”(基本上是 transaction# detail#)，并递增此组合的序列号(因此，例如，我将有两行用于 txn# 1、detail# 1，第一行有 seq#1，第二行有 seq#2，如果 txn#513、detail#44 有 3 个重复行，这些将有seq# 1,2,3 适当。这将是理想的(从支持的角度来看)，如果数据在申请前和申请后的变化是相似的。是否有一些简单的方法来创建这个列，或者我需要手动循环行，每次 txn# 或 detail# 更改时重置使用的序列？

编辑添加:应用程序部署在 Postgresql 7.4 和 8.1 上，解决方案需要适用于这两个版本。

Answer 1

如果您的列“几乎是唯一的”，我。 e.每个值没有太多重复项，您可以使用以下语法:

ALTER TABLE mytable ADD seq INT;

UPDATE  mytable mo
SET     seq = 
        (
        SELECT  COUNT(*)
        FROM    mytable mi
        WHERE   mi.txn = mo.txn
                AND mi.detail = mo.detail
                AND mi.ctid <= mo.ctid
        )

ALTER TABLE mytable ALTER COLUMN seq SET NOT NULL;

ALTER TABLE mytable DROP CONSTRAINT t_mytable_pkey; -- the name of your current primary key

ALTER TABLE mytable ADD CONSTRAINT t_mytable_pkey PRIMARY KEY (txn, detail, seq);

如果没有太多重复，这将很快完成。