avg() with floating-point types

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)?

Responses

Browse pgsql-sql by date

  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