复合键每组行的序列号

Question

我试图维护一个地址历史表:

CREATE TABLE address_history (
    person_id int, 
    sequence int,
    timestamp datetime default current_timestamp,
    address text,
    original_address text,
    previous_address text,
    PRIMARY KEY(person_id, sequence),
    FOREIGN KEY(person_id) REFERENCES people.id
);

我想知道是否有一种简单的方法可以在address_history中自动编号/约束序列，以便为每个 从1开始自动计数。

换句话说，person_id = 1 的第一行将获得序列 = 1;person_id = 1 的第二行将获得序列 = 2。第一行person_id = 2，将再次得到序列 = 1。
另外，有没有更好/内置的方式来维护这样的历史记录？

Answer 1

不要。已经试过很多次了，很痛苦。

使用普通序列号或标识列：

• 自动递增表列

CREATE TABLE address_history (
  address_history_id serial PRIMARY KEY
, person_id int NOT NULL REFERENCES people(id)
, created_at timestamp NOT NULL DEFAULT current_timestamp
, previous_address text
);

使用窗口函数 row_number（） 获取每个person_id没有间隙的序列号。您可以保留一个 VIEW，您可以在查询中将其用作表的直接替换，以准备好这些数字：

CREATE VIEW address_history_nr AS
SELECT *, row_number() OVER (PARTITION BY person_id
                             ORDER BY address_history_id) AS adr_nr
FROM   address_history;

请参见：

< li >涉及多个表的多个事务的无间隙序列

或者您可能想按其他方式订购。也许是created_at？更好的created_at，address_history_id打破可能的联系。相关答案：

• 具有备用序列的列

此外，您要查找的数据类型是时间戳或timestamptz，而不是<罢工>日期时间Postgres中的：

• 在Rails和PostgreSQL中完全忽略时区

您只需要存储previous_address（或更多详细信息），而不是<罢工>地址 ，也不是<罢工>原始地址 。在健全的数据模型中，两者都是多余的。