复合键组每行的序列号 [英] 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
);

我想知道是否有一个简单的方法来自动编号/约束 / code>在 address_history 中为每个 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?

推荐答案

p>不要。

使用一个简单的串行列：

• 自动增加SQL功能

• Auto increment SQL function

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
);

使用窗口函数 到$获取序列号，每个 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:

• 具有替代序列的列

• Column with alternate serials

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

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

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

• Ignoring timezones altogether in Rails and PostgreSQL

而您只需要存储 previous_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 db layout.

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