Skip site navigation (1) Skip section navigation (2)

Re: Implementation of a updateable, "temporal" view on data

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 (view raw or flat)
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

In response to

pgsql-novice by date

Next:From: Rodrigo De LeónDate: 2007-10-17 14:13:04
Subject: Re: Implementation of a updateable, "temporal" view on data
Previous:From: Richard Broersma JrDate: 2007-10-16 22:06:59
Subject: Re: Implementation of a updateable, "temporal" view on data

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group