Re: Filling null values - Mailing list pgsql-general

From Sim Zacks
Subject Re: Filling null values
Date
Msg-id 4E3E2B11.3090503@compulab.co.il
Whole thread Raw
In response to Filling null values  (jeffrey <johjeffrey@hotmail.com>)
Responses Re: Filling null values  (Ben Carbery <ben.carbery@gmail.com>)
List pgsql-general
On 08/05/2011 07:32 PM, jeffrey wrote:
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?

Thanks,
Jeff

You need a primary key on the table to do this. Then you can do a self join and update the correct values.

Sim

pgsql-general by date:

Previous
From: Craig Ringer
Date:
Subject: Re: FREE hosting platforms with PostgreSQL, Java SDK, Tomcat, ecc.?
Next
From: Stuart McGraw
Date:
Subject: Re: FREE hosting platforms with PostgreSQL, Java SDK, Tomcat, ecc.?