From: | "George Pavlov" <gpavlov(at)mynewplace(dot)com> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | avg() with floating-point types |
Date: | 2006-01-02 00:40:18 |
Message-ID: | CCB89282FCE1024EA3DCE687A96A516403AB93C2@ehost010-6.exch010.intermedia.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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_code cpc 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.0000000000003333
41.078385733333 | 41.0783857333333333 | -0.0000000000003333
31.576437888889 | 31.5764378888888889 | 0.0000000000001111
42.666669666667 | 42.6666696666666667 | 0.0000000000003333
35.104581166667 | 35.1045811666666667 | 0.0000000000003333
31.263006142857 | 31.2630061428571429 | -0.0000000000001429
38.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)?
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2006-01-02 01:25:50 | Re: avg() with floating-point types |
Previous Message | chester c young | 2006-01-01 18:22:17 | exceptions in rules |