byron509 wrote
> Hi, hoping someone can help me understand the following in 64-9.2:
>
> I am casting a mathematical difference to integer in order to truncate to
> the whole number of the result (not the rounded value). All example
> variables are stored as integers as well as the results.
>
> 1. So query with example column values is:
>
> SELECT CAST(((1400-42.32)/100) AS integer);
> Solves as:
> CAST((1357.68/100) AS integer);
> Solves as:
> CAST(13.5768 AS integer);
> Results in 14. This is not what I want.
>
> 2. But if I query:
>
> SELECT CAST(1358/100 AS integer);
> Solves as:
> SELECT CAST(13 AS integer);
> Result is 13. Which is what I want. But I am guessing this is because
> 1358 is seen as an integer by the planner.
>
> 3. So I thought if I rounded I would get the answer I want.
>
> SELECT CAST(ROUND((1400-42.32),0)/100 AS integer);
> Solves as:
> SELECT CAST(1358/100 AS integer);
> Result is 14!
>
> 4. I have found a solution as:
>
> SELECT CAST(CAST((1400-42.32) AS integer)/100 AS integer);
> Solves as:
> SELECT CAST(CAST(1357.68 AS integer)/100 AS integer);
> Solves as:
> SELECT CAST(1358/100 AS integer);
> Result is 13.
>
> So my question is, what is the reason for the different result between 1.
> & 4.; and why does 3 not work?
Integer division truncates any remainder.
Casting numeric to integer applies default rounding rules.
1) casts numeric to integer
2) integer division
3) casts numeric to integer -> the result of the round function is the same
type as its input, which in this case is numeric.
4) This does not actually work - your result is symptomatic of the input
data.
the CAST(1357.68 AS integer) results in an integer 1358 which is then fed
into integer division.
Imagine if the input resulted in CAST(1399.52 AS integer)/100 - the result
would be 14, not 13, since the cast would round up to 1400.
SELECT CAST(CAST((1400 - 0.48) AS integer)/100 AS integer)
A very quick search of the PostgreSQL documentation:
http://www.postgresql.org/docs/9.3/interactive/functions-math.html
Turns up the handy "trunc(dp or numeric)" function...
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Behavior-of-CAST-to-integer-tp5803961p5803963.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
