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.
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.
So this prompts the question how does postgresql decide what types to
cast arguments to. It seems thus far if a decimal is found in the
argument its numeric and everything else is assumed to be integer if it
does not contain a decimal point.
Craig Ringer wrote:
Justin wrote:
I tried casting them to numeric and it was still wrong
How do the results differ from what you expect? You've posted a bunch
of code, but haven't explained what you think is wrong with the results.
Can you post a couple of SMALL examples and explain how the results
are different from what you expect them to be?
Try the example using the following formats for the literals in your
test:
2.0
'2.0'::numeric (this is a BCD decimal)
'2.0'::float4 (this is a C++/IEEE "float")
'2.0'::float8 (this is a C++/IEEE "double")
and see how the results differ.
--
Craig Riniger
