Re: Problem with query on history table

Probably the easiest way is to switch to using table partitioning and
switch to using start_timestamp and end_timestamp, so that when you
modify a row you update the old one setting end_timestamp to now() and
insert the new row (all within one transaction).

There are other ways to do it, but they'll probably be much slower. I
don't think they require a lot of CASE statements though.

Show us what you were planning on doing and maybe I'll have more ideas.

On Mon, Feb 27, 2006 at 08:19:30AM +0100, Andreas Joseph Krogh wrote:
> Hi all!
> 
> I don't know if there's a standard solution to the kind of problem I'm trying 
> to solve, but I will appreciate your thougts(and maybe solution:) on this 
> problem of mine:
> 
> I have 2 tables: hist and curr which hold numbers for "history-data" and 
> "current-data" respectivly. Here is a simplified version of the schema:
> 
> CREATE TABLE curr (
>     id integer NOT NULL,
>     etc integer NOT NULL,
>     created timestamp without time zone NOT NULL,
>     modified timestamp without time zone
> );
> 
> CREATE TABLE hist (
>     id serial NOT NULL,
>     curr_id integer NOT NULL REFERENCES curr(id),
>     etc integer NOT NULL,
>     modified timestamp without time zone NOT NULL
> );
> 
> andreak=# SELECT * from curr;
>  id | etc |       created       |      modified
> ----+-----+---------------------+---------------------
>   1 |   5 | 2006-02-01 00:00:00 |
>   2 |  10 | 2006-01-15 00:00:00 | 2006-01-26 00:00:00
>   3 |  10 | 2006-01-08 00:00:00 | 2006-01-25 00:00:00
> (3 rows)
> 
> andreak=# SELECT * from hist;
>  id | curr_id | etc |      modified
> ----+--------+-----+---------------------
>   1 |       3 |  30 | 2006-01-16 00:00:00
>   2 |       3 |  20 | 2006-01-25 00:00:00
>   3 |       2 |  20 | 2006-01-26 00:00:00
> (3 rows)
> 
> Now - I would like to get a report on what the "ETC" is on a given entry in 
> "curr" in a given "point in time". Let me explain. If I want status for 17. 
> jan.(17.01.2006) I would like to get these numbers out from the query:
> 
>  id |       created       |    curr_modified    |    hist_modified    | etc
> ----+---------------------+---------------------+---------------------+-----
>   3 | 2006-01-08 00:00:00 | 2006-01-25 00:00:00 | 2006-01-16 00:00:00 |  30
>   2 | 2006-01-15 00:00:00 | 2006-01-26 00:00:00 | 2006-01-26 00:00:00 |  20
>   1 | 2006-02-01 00:00:00 |                     |                     |   5
> 
> 
> That is; If the entry is modified after it's created, a snapshot of the "old 
> version" is copied to table "hist" with the hist.modified field set to the 
> "modified-timestamp". So there will exist several entries in "hist" for each 
> time an entry in "curr" is modified.
> 
> If I want status for the 27. jan. I would like the query to return the 
> following rows:
> 
>  id |       created       |    curr_modified    |    hist_modified    | etc
> ----+---------------------+---------------------+---------------------+-----
>   3 | 2006-01-08 00:00:00 | 2006-01-25 00:00:00 | 2006-01-25 00:00:00 |  10
>   2 | 2006-01-15 00:00:00 | 2006-01-26 00:00:00 | 2006-01-26 00:00:00 |  10
>   1 | 2006-02-01 00:00:00 |                     |                     |   5
> 
> select curr.id, curr.created, curr.modified as curr_modified, hist.modified as 
> hist_modified, coalesce(hist.etc, curr.etc) as etc FROM curr LEFT OUTER JOIN 
> hist ON(curr.id = hist.curr_id) WHERE ...
> 
> I'm really stuck here. It seems to me that I need a lot of 
> CASE...WHEN...ELSE.. statements in the query, but is there an easier way?
> 
> --
> Andreas Joseph Krogh <andreak@officenet.no>
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faq
> 

-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461