Re: Behavior of CAST to integer

On 15/05/14 10:05, 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?
>
> Much appreciation on any pointers.
> BC
>
>
>
> --
> View this message in context: http://postgresql.1045698.n5.nabble.com/Behavior-of-CAST-to-integer-tp5803961.html
> Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
>
>
You need the floor operator:

gavin=> SELECT floor((1400-42.32)/100);
floor
-------
13
(1 row)

gavin=> SELECT CAST(floor((1400-42.32)/100) AS integer);
floor
-------
13
(1 row)

PostgreSQL has many useful mathematical functions documented.

Cheers,
Gavin