Re: How to handle things that change over time? - Mailing list pgsql-general
| From | Robert Heinen |
|---|---|
| Subject | Re: How to handle things that change over time? |
| Date | |
| Msg-id | CAKQp+O=QiS0RAH=57YdAGcJehZ8WrMj8_f=Qw=e-yC-cvOERcA@mail.gmail.com Whole thread Raw |
| In response to | How to handle things that change over time? (stan <stanb@panix.com>) |
| List | pgsql-general |
I"m a big fan of a table / table_history combo. Meaning, if you have a person and that person can have different states that change frequently, you can do something like this:
create table people(
person_id
person_name
)
create table people_state_history(
person_id references people,effective tsrange not nulldefault tsrange(clock_timestamp()::timestamp without time zone, 'infinity'),
primary key (person_id, effective),
person_state
)
Use a constraint so you can't have overlapping effective ranges:
alter table person_state_history add
constraint no_overlapping_person_state_history
exclude using gist (
cast(person_id as text ) with =,
effective with &&);
alter table person_state_history add
constraint no_overlapping_person_state_history
exclude using gist (
cast(person_id as text ) with =,
effective with &&);
Then, to see a person's state at the current time, you can do something like this:
select person_id, person_state from people_state_history where effective <@ now()::timestamp without time zone
(and add your joins in as well).
We've also created a trigger, so any new insert on the state_history table will automatically close the last row.
create or replace function set_event_history_in_use ()
returns trigger
as
$$
begin
update person_state_history
set effective = tsrange(lower(effective),
clock_timestamp()::timestamp without time zone)
where clock_timestamp()::timestamp without time zone <@ effective
and person_id = NEW.person_id;
NEW.effective := tsrange(clock_timestamp()::timestamp without time zone, 'infinity');
return NEW;
end;
$$
language plpgsql;
returns trigger
as
$$
begin
update person_state_history
set effective = tsrange(lower(effective),
clock_timestamp()::timestamp without time zone)
where clock_timestamp()::timestamp without time zone <@ effective
and person_id = NEW.person_id;
NEW.effective := tsrange(clock_timestamp()::timestamp without time zone, 'infinity');
return NEW;
end;
$$
language plpgsql;
Then, depending on business requirements, you can create this dual table to track history across time. If salaries are something people would like to see over time, then you could do a person_salary_history table, etc.
Hope this helps!
Best,
Rob Heinen
On Fri, Sep 13, 2019 at 11:57 AM stan <stanb@panix.com> wrote:
I am working on a system that will support internal bossiness work for a
company. Periodicly things will change in their "world". For example they
periodically recalculate the "mark up" of various components, of their
bushiness, such as labor and or purchased materials. Presently I am keeping
these constants in a table, and I have the constant, and an effective start,
and end date for these factors. In this scenario, the number of rows will
just grow over time, and by using the proper conditions in our select
statement, i can apply the correct factors as they change over time.
In another scenario, a column of the employee table is the pay rate.
Obviously this will change over time, also. It does not appear to me that
this lends itself to this same treatment, as most of the data associated
with a given employee, is fairly static, and if I add an entire row, just
because the pay rate changed, this looks overly complex.
This cannot be the first time this issue has been addressed. What have
others done in this scenario?
pgsql-general by date: