From: | Robert Heinen <rob(at)216software(dot)com> |
---|---|
To: | stan <stanb(at)panix(dot)com> |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: How to handle things that change over time? |
Date: | 2019-09-13 12:10:02 |
Message-ID: | CAKQp+O=QiS0RAH=57YdAGcJehZ8WrMj8_f=Qw=e-yC-cvOERcA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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 null
default 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 &&);
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;
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(at)panix(dot)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?
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | George Neuner | 2019-09-13 13:27:04 | Re: Web GUI for PG table ? |
Previous Message | Achilleas Mantzios | 2019-09-13 10:49:50 | Re: pgbouncer with ldap |