-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of jeffrey
Sent: Friday, August 05, 2011 12:33 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Filling null values
I have a table that looks like this:
homeid city date measurement pre/post
123 san francisco 1/2/2003 1458 pre
123 san francisco NULL 1932 post
124 los angeles 2/4/2005 938 pre
124 NULL NULL 266 pre
124 los angeles 7/4/2006 777 post
I'd like to write a query so that I get the following result:
homeid city date measurement pre/post
123 san francisco 1/2/2003 1458 pre
123 san francisco 1/2/2003 1932 post
124 los angeles 2/4/2005 938 pre
124 los angeles 2/4/2005 266 pre
124 los angeles 7/4/2006 777 post
If a city or date is null, then it will fill from other not null values with
the same homeid. If given the choice, it will preferentially fill from a
row where homeid AND pre/post match. But if that doesn't match, then it
will still fill from the same homeid.
Does anyone have ideas for this?
---------------------------------------------------------------
What version are you using? You may be able to accomplish your goals with
Window functions.
If "homeid" determines "city" you should just remove "city" from the table
altogether and create a lookup table where "homeid" is the PK and "city" is
one of the other columns. Your date issue is somewhat more problematic to
correctly address.
As an alternative to Window functions you'd probably want to, generally,
create lookup tables. I just described the "homeid" lookup table but the
date lookup table is a little more complicated. The general idea would be
to ORDER and NUMBER the current records and then create a master lookup
using ROW_NUMBER, "homeid", and "date". Then, for any rows missing a date
you point into the lookup table and use the date from the lookup record with
the largest ROW_NUMBER less than the current row.
David J.