[SQL] plpgsql triggers in rules
is is possible for to have a "do instead" trigger on a view that is a plpgsql function? __ Yahoo! DSL Something to write home about. Just $16.99/mo. or less. dsl.yahoo.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] session variables in 8.0
understand that in 8.0 pg has session variables. cannot find in doc how to use them. __ Yahoo! DSL Something to write home about. Just $16.99/mo. or less. dsl.yahoo.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] exceptions in rules
is there any way within a rule to raise an exception? __ Yahoo! for Good - Make a difference this year. http://brand.yahoo.com/cybergivingweek2005/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] avg() with floating-point types
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.70937433 | 36.70937433 | -0. 41.07838573 | 41.07838573 | -0. 31.57643789 | 31.57643789 | 0. 42.6967 | 42.6967 | 0. 35.10458117 | 35.10458117 | 0. 31.263006142857 | 31.2630061428571429 | -0.1429 38.805648772727 | 38.8056487727272727 | -0.2727 ... 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)? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] avg() with floating-point types
"George Pavlov" <[EMAIL PROTECTED]> writes: > 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: You're forcing the result of the avg() calculation to be rounded to 12 digits when you store it into city.latitude. Your example simply shows that the avg() calculation is being done to more precision than that. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] avg() with floating-point types
On Sun, Jan 01, 2006 at 04:40:18PM -0800, George Pavlov wrote: > 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: You've declared the numeric column as (16,12) so the inserted values are rounded to the 12th decimal place. Notice that that's where the values start to differ: > latitude | avg |diff > -+-+- > 36.70937433 | 36.70937433 | -0. Is such a difference significant to your application? The distance discrepancy is on the order of tens of nanometers, which seems absurdly precise. With lat/lon coordinates five places after the decimal point is about meter-precision, which is probably more than adequate for whatever you're doing. Incidentally, if you're doing anything geospatial then you might want to check out PostGIS: http://www.postgis.org/ If you look around you should be able to find shapefiles with points or polygons for cities and postal codes. -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
