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
);
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
.
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.
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
);
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;
See:
Or you might want to ORDER BY
something else. Maybe created_at
? Better created_at, address_history_id
to break possible ties. Related answer:
Also, the data type you are looking for is timestamp
or timestamptz
, not in Postgres:datetime
And you only need to store previous_address
(or more details), not , nor address
. Both would be redundant in a sane data model.original_address
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With