Números de serie por grupo de filas para key compuesta

Estoy tratando de mantener una tabla de historial de direcciones:

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

Me pregunto si existe una manera fácil de autonumerar / restringir la sequence en address_history para contar automáticamente desde 1 para cada person_id .

En otras palabras, la primera fila con person_id = 1 obtendría sequence = 1 ; la segunda fila con person_id = 1 obtendría sequence = 2 . La primera fila con person_id = 2 , obtendría sequence = 1 nuevamente. Etc.
Además, ¿existe una forma mejor / integrada de mantener una historia como esta?

    No lo hagas Ha sido probado muchas veces y es un dolor.

    Use una columna de serial simple:

    • Aumento automático de la function de SQL

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

    Utilice la function de window row_number() para get numbers de serie sin espacios por person_id . Puede persistir una VIEW que puede usar como reemploop directo para su table en consultas para tener esos numbers listos:

     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; 

    O quizás desee ORDER BY otra cosa. ¿Tal vez created_at ? Better created_at, address_history_id para romper posibles vínculos. Respuesta relacionada:

    • Columna con publicaciones seriadas alternativas

    Además, el tipo de datos que está buscando es timestamp o timestamptz , no datetime en Postgres:

    • Haciendo caso omiso de las zonas horarias por completo en Rails y PostgreSQL

    Y solo necesita almacenar previous_address (o más detalles), no address , ni original_address . Ambos serían networkingundantes en un layout de db cuerdo.