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@hotmail.com <scorpdaddy@hotmail.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@gmail.com> > Date: Tue, Oct 26, 2010 6:16 am > Subject: [NOVICE] Need help with a query > To: <pgsql-novice@postgresql.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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-novice > > > >