On Mon, May 12, 2008 at 06:37:02PM -0400, Justin wrote: > I guess i have not been very clear. > > lets take this > select (9/10), (9/10)::numeric, (9::numeric/10::numeric), (9./10), > (9*.1) > > With the given select statement i expected the results all to be same, > especially sense it cast 4 of the 5 to numeric either with explicit cast > or by containing a decimal. Instead postgresql cast the first 2 > calculations to integer, it then uses integer math so the result is 0. > > To Add further conversion to my small brain there is a specific type > cast to the second calculation but it still returned 0. Not what i > would have expected. After thinking about it for say 10 seconds, i see > that Postgresql is following the order of operation in the 2nd > calculation where it does integer math then cast the results to numeric. PG does very similar things to what C does. '9' is an integer literal, and so is '10', there is a '/' operator that takes two integers and returns an integer and this gets used, resulting in an integer. If you happen to cast the thing to a value of numeric type this will happen after the division (i.e. it follows the syntax, like C does). Casting the integers to values of numeric type is similar, just the numeric version of the division operator gets used. The last example exercises a different code path, in that '9.' is a value of numeric type and '10' is still of integer type. There's some magic somewhere in PG that says that values of numeric type are more expressive than values of integer type causing the parser (I'm guessing here) to insert a cast to numeric type. The types now unify and one value can be divided by the other. The magic seems somewhat arbitrary; what if I wanted to go to the less precise type or generally be told when things didn't unify. > I made the incorrect assumption Postgresql would have casted all the > arguments to numeric then done the math. After thinking this through > for a short bit i see why postgresql is casting the arguments to integer > type as numeric/floating point math can be a pretty heavy hit > performance wise. I don't think it's accurate to say the behaviour is there because of performance reasons, it's just evaluating your code as you've written it. The behaviour you describe is closer to an untyped (i.e. dynamically checked, or as they seem to be popularly known "weakly typed") scripting language. Either that or something like Haskell which treats types much more rigorously than PG, where the expression (9.0 / (10::Int)) would fail to type check, and 9.0/10 (or even 9/10) would do what you wanted and parse 10 as any value that implements the fractional type class (probably a floating point number). The easiest way to understand what's going on is generally playing with a single expression, then changing the literals to represent values of different types and seeing how the result changes. You may get some mileage out of using EXPLAIN VERBOSE (you can see the cast being inserted in the 9./10 case, when compared to 9/10---function OID 1740 takes an int4 and returns a numeric) but it's somewhat difficult to read. Sam
