avg() with floating-point types
От | George Pavlov |
---|---|
Тема | avg() with floating-point types |
Дата | |
Msg-id | CCB89282FCE1024EA3DCE687A96A516403AB93C2@ehost010-6.exch010.intermedia.net обсуждение исходный текст |
Ответы |
Re: avg() with floating-point types
Re: avg() with floating-point types |
Список | pgsql-sql |
I have city and postal_code tables linked by city_postal_code through a city_id and postal_code_id. The postal_codes have latitude/longitude, the cities don't. I want to set the city lat/long to the average of the associated postal codes (abstract for a minute on whether that actually makes sense from a geographical perspective), so I have a statement: update city set latitude = city2.lat from (select c.city_id, avg(pc.latitude) as lat from city c left join city_postal_codecpc using (city_id) left join postal_code pc using (postal_code_id) group by c.city_id) city2 where city2.city_id = city.city_id The datatype of both city.latitude and postal_code.latitude is number(16,12). This works, but I would like to understand why there is sometimes a discrepancy between avg(pc.latitude) and what actually gets inserted into the city table -- is it the usual floating-point discrepancy or is there something I can do about it? E.g. after the above update: select c.latitude, avg(pc.latitude), c.latitude-avg(pc.latitude) as diff from city c left join city_postal_code cpc using(city_id) left join postal_code pc using (postal_code_id) group by c.city_id,c.latitude having avg(pc.latitude)!= c.latitude latitude | avg | diff -----------------+---------------------+---------------------36.709374333333 | 36.7093743333333333 | -0.000000000000333341.078385733333| 41.0783857333333333 | -0.000000000000333331.576437888889 | 31.5764378888888889 | 0.000000000000111142.666669666667| 42.6666696666666667 | 0.000000000000333335.104581166667 | 35.1045811666666667 | 0.000000000000333331.263006142857| 31.2630061428571429 | -0.000000000000142938.805648772727 | 38.8056487727272727 | -0.0000000000002727 ... An additional question -- is the UPDATE above written as cleanly as possible (I am not very confident on my understanding of UPDATE-SET-FROM syntax)?
В списке pgsql-sql по дате отправления: