Thread: Behavior of CAST to integer

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.

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

Thanks for the solutions gentleman. I really appreciate it.

Usually I am using postgres/postgis for geographic analysis and large (but
static) data manipulation.  This threw me for a little loop as I was
requested to translate some FORTRAN code into SQL.  Well you can imagine
what kind of fun that is.  So I was stuck trying to mimic that instead of
finding an alternate function.

B



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Behavior-of-CAST-to-integer-tp5803961p5804103.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.