From: Michael Fuhr <mike@fuhr.org>
To: mrblonde@locked.myftp.org
Cc: pgsql-sql@postgresql.org
Subject: Re: update from multiple rows
Date: Sat, 22 Jan 2005 12:35:39 -0700
Message-ID: <20050122193539.GA88448@winnie.fuhr.org> (raw)
In-Reply-To: <BAY13-F384058140ADAAD16F8BE889830@phx.gbl>
References: <BAY13-F384058140ADAAD16F8BE889830@phx.gbl>
On Sat, Jan 22, 2005 at 12:51:20PM +0000, adam etienne wrote:
>
> I have some trouble updating a table like this one :
> date | data_raw | data_sys
> 12-01 | 5 | 4.5
> 13-01 | 6 | 6
> 14-01 | 7 | 8
>
> I would like to update the 'data_sys' row by computing values of multiple
> 'data_raw' values. I mean for example :
> data_sys(13-01) = (data_raw['12-01'] + data_raw['13-01'] +
> data_raw['14-01'] )/3;
Is there a reason to maintain data_sys in the table? Could you use
a view instead? A view could do self joins (join the table against
itself) and perform the necessary calculations on the fly when you
make a query. That could be expensive if you select large data
sets, but for small slices of data it might suffice.
Another possibility might be to use a trigger to recalculate data_sys
when records are inserted, updated, or deleted. You'd still have
to do a potentially expensive one-time update of the entire table,
but future updates would then touch only the rows that depend on
the data being inserted, updated, or deleted, and the calculated
values would always be current. Using a trigger would require some
care, however, to avoid cascading updates that are unnecessary or
that could result in infinite recursion.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mike@fuhr.org, mrblonde@locked.myftp.org
Subject: Re: update from multiple rows
In-Reply-To: <20050122193539.GA88448@winnie.fuhr.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox