From: | Hans-Peter Oeri <hp(at)oeri(dot)ch> |
---|---|
To: | Richard Broersma Jr <rabroersma(at)yahoo(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Implementation of a updateable, "temporal" view on data |
Date: | 2007-10-17 13:14:54 |
Message-ID: | 47160ACE.5060409@oeri.ch |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Hi!
Richard Broersma Jr wrote:
> This way your current record stays current and you simply insert
> history records.
Sometimes there is an obvious easy way ;) Thanks a lot!
However, I had to adapt the solution a little:
In order to avoid (undeferrable) primary key conflicts, I have to
*first* move the start time 'out of the way', then insert the new row
(with corrected start time).
For the archives:
UPDATE table SET start = start + '00:00:00.10'::interval
WHERE table.id = old.id AND table.stop > '2037-12-01
00:00:00'::timestamp without time zone;
-- 2037-12-XX being my definition of 'eternity'
INSERT INTO table(id,start,stop,val,...)
VALUES (old.id, old.start - '00:00:00.10'::interval, now(), old.val ...);
UPDATE table SET start = now(), val=...
WHERE table.id = old.id AND table.stop > '2037-12-01
00:00:00'::timestamp without time zone;
(The short time that (errorously) two periods 'overlap' has to be taken
into account for checks!)
HPO
From | Date | Subject | |
---|---|---|---|
Next Message | Rodrigo De León | 2007-10-17 14:13:04 | Re: Implementation of a updateable, "temporal" view on data |
Previous Message | Richard Broersma Jr | 2007-10-16 22:06:59 | Re: Implementation of a updateable, "temporal" view on data |