复合键每组行的序列号 [英] Serial numbers per group of rows for compound key

问题描述

我正在尝试维护地址历史记录表:

I am trying to maintain an address history table:

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 中的 sequence，以便为每个 person_id 自动从 1 开始计数.

I'm wondering if there's an easy way to autonumber/constrain sequence in address_history to automatically count up from 1 for each person_id.

换句话说，person_id = 1 的第一行会得到 sequence = 1；person_id = 1 的第二行将得到 sequence = 2.person_id = 2 的第一行，将再次获得 sequence = 1.等
另外，有没有更好的/内置的方式来维护这样的历史记录?

In other words, the first row with person_id = 1 would get sequence = 1; the second row with person_id = 1 would get sequence = 2. The first row with person_id = 2, would get sequence = 1 again. Etc.
Also, is there a better / built-in way to maintain a history like this?

推荐答案

不要.试过很多次了，很痛苦.

Don't. It has been tried many times and it's a pain.

使用普通的 serial 或 IDENTITY 列:

Use a plain serial or IDENTITY column:

• 自增表列

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可以用作查询中表格的替代品，以准备好这些数字:

Use the window function row_number() to get serial numbers without gaps per person_id. You could persist a VIEW that you can use as drop-in replacement for your table in queries to have those numbers ready:

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;

见:

• 差距- 涉及多个表的多个事务的较少序列

或者你可能想要ORDER BY别的东西.也许created_at?更好的 created_at, address_history_id 打破可能的联系.相关答案:

Or you might want to ORDER BY something else. Maybe created_at? Better created_at, address_history_id to break possible ties. Related answer:

• 带有备用序列号的列

此外，您要查找的数据类型是 timestamp 或 timestamptz，而不是 Postgres 中的 datetime:

Also, the data type you are looking for is timestamp or timestamptz, not datetime in Postgres:

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

而且您只需要存储previous_address(或更多详细信息)，而不是address，也不是original_address.在健全的数据模型中，两者都是多余的.

And you only need to store previous_address (or more details), not address, nor original_address. Both would be redundant in a sane data model.

这篇关于复合键每组行的序列号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！