|
|
| Applications |
| Mailing Lists |
| Miscellaneous |
| Operating Systems |
| Programming |
Re: [NOVICE] Implementation of a updateable, "temporal" view on data
Date: Wed Oct 17 2007 - 09:14:54 EDT
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
---------------------------(end of broadcast)---------------------------TIP 6: explain analyze is your friend Received on Wed Oct 17 09:17:40 2007
This archive was generated by hypermail 2.1.8 : Thu Jun 19 2008 - 00:03:16 EDT
|
Contact Us Legal Notices Order Services Online Pantek Home Privacy Policy IT news Site Map Pantek Library |