Replace Nulls with Values Based on 2 Keys (Column Values), Postgresql

Question

I gave up on finding my own answer to do the following (sigh) :

city_name	lat	lng
a	1	10
NULL	2	20
NULL	3	30
b	2	20
c	3	30

I am working on a large data set containing a lot of nulls in the column like 'city_name' above. Based on the value in 'lat', 'lng,' I know the first null is 'b' and the second null is 'c'.

The table has over 1000 distinct city names and 15000 nulls that can be corrected by finding the rows having the same lat and lng value. It seems impossible to manually replace nulls with the city name based on latitude(lat column) and longitude(lng column).

I want null value to be the city name based on lat and lng in the same row. Maybe there's a way to refer to other rows having the same lat and lng, get the city name from them and then replace null with the city name?

Is there a way to replace all nulls with the city name at once like the above in POSTGRESQL?

Thanks in advance!

Gordon Linoff · Accepted Answer · 2021-05-30 16:31:04Z

1

In a query you can use:

select t.*,
       max(city_name) over (partition by lat, lng) as imputed_cityname
from t;

If you wanted to update the null values, you can use:

update t
    set city_name = ll.city_name
    from (select lat, lng, max(city_name) as city_name
          from t
          group by lat, lng
         ) ll
    where ll.lat = t.lat and ll.lng = t.lng and
          t.city_name is null;

Here is a db<>fiddle.

edited May 30, 2021 at 16:31

answered May 30, 2021 at 16:13

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Gordon Linoff Over a year ago

@DanK . . . Of course it works. I added a db<>fiddle.

Dan K Over a year ago

Yes! I checked it out again, and confirmed that it does!! Thanks again, Gordon!

Dan K Over a year ago

I have another question, Gordon. I tried to do the same as the above adding conditions like where clause in the subquery and outer. But it updates more rows than expected. Do I have to change any when I want to fill in nulls with particular conditions?

Gordon Linoff Over a year ago

Please ask another question with a proper explanation and the query you are using.

Collectives™ on Stack Overflow

Replace Nulls with Values Based on 2 Keys (Column Values), Postgresql

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related