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

Noob Q collapsing multiple rows into one via case and max

From: Kenneth Tilton <ktilton(at)mcna(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Noob Q collapsing multiple rows into one via case and max
Date: 2011-01-06 22:50:26
Message-ID: 4D264732.2030105@mcna.net (view raw or flat)
Thread:
Lists: pgsql-general
[a meta-question for all the below is "what's a good link for hairy SQL"?]

A while ago I worked on a project where we had some hairy SQL collapsing 
multiple rows of pseudo-rdf triples (columns subject,predicate, and 
object) into one flattened row in which a hard-coded case/max (I forget 
the exact syntax) plucked out a value for a column "X" by looking for a 
row with the predicate "X". So we'd find all the rows for one subject (a 
logical row ID) and collapse it into a single row of conventional columns.

I now want to do something similar, but combining a conventional XY 
table with columns X and Y and a separate change-history table with 
columns row-id, column-name, value, and last-time-in-effect, to which 
old values get pushed by an update trigger on XY when X or Y change.

The goal is to specify a time in the past and recreate a row of XY as of 
that time, by starting from the current row and replacing X and Y if 
necessary by the value that would have been current then, ie with the 
max last-time-in-effect <= the specified time.

My thought was to read XY for a desired row-id with a left join to the 
change-history table where last-time-in-effect <= the specified time 
(pretty obvious) but the stumper is the clause for the x-then and y-then 
columns. A useless start:

     case when column_name = "X" then change_history.value else XY.X end 
case as x-then

I had some luck with that, but I also want to pick out the most recent 
change up to the target time where there were multiple changes.

kt

ps. Is this a crazy way to achieve our requirement, which is to see the 
DB as it was at a point in the past? Our data is incredibly stable, btw. kt

Responses

pgsql-general by date

Next:From: Kenneth TiltonDate: 2011-01-06 23:12:57
Subject: Re: Noob Q collapsing multiple rows into one via case and max
Previous:From: Alban HertroysDate: 2011-01-06 22:06:55
Subject: Re: UUID column as pimrary key?

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