Re: Need help with a query

On Tue, Oct 26, 2010 at 7:05 AM, A B <gentosaker(at)gmail(dot)com> wrote:

> in this case new x-y pairs always goes into foo first.
> One could consider creating a view, but would a view be efficient in
> this case? A new row in bar, would that not cause it to select max()
> value for the x,y pair again, i.e. scan the table bar for the specific
> x,y values, or will it be smart enough to do only one comparison?
>
>
A view doesn't "do" anything unless it is selected. That said, each time
the view is selected, it will need to compute max(seen).

Sean

>
> 2010/10/26 scorpdaddy(at)hotmail(dot)com <scorpdaddy(at)hotmail(dot)com>:
> > Is it necessary that this is a table? Because it seems a lot like a view
> of
> > the same data actually. CREATE VIEW ...
> >
> > The expected UPDATE query for a table can be problematic. I have a
> similar
> > issue in 1 of my DB's. New records - new x, y - get written to bar and
> do
> > not yet have a corresponding x, y record in foo. So when the UPDATE runs
> it
> > ignores the new records. One can have an after insert trigger to keep
> foo
> > up to date with bar. But such machinations arise because foo is only a
> view
> > of the same data in bar.
> >
> >
> >
> > ----- Reply message -----
> > From: "A B" <gentosaker(at)gmail(dot)com>
> > Date: Tue, Oct 26, 2010 6:16 am
> > Subject: [NOVICE] Need help with a query
> > To: <pgsql-novice(at)postgresql(dot)org>
> >
> > Hello.
> >
> > I'm having a problem with a probably very simple query.
> > I need to update a table
> > foo (x int, y int, last_seen timestamp, unique(x,y));
> >
> > where the values should be taken from a larger table
> > bar( x int, y int, seen timestamp);
> > where each x,y combination occurs several times, and the value in
> > foo.last_seen should be the value max(seen) for each pair of x,y.
> > Notice! All combinations of x,y in bar are also in foo, but not the
> > other way around.
> >
> > So how do I write a query for this?
> > Thanks in advance.
> >
> > --
> > Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-novice
> >
> >
> >
> >
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>