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
>