Re: Rounding Double Precision or Numeric

This is documented in section 8.1.2 in the manual. (
https://www.postgresql.org/docs/9.6/static/datatype-numeric.html)

NUMERIC rounds away from zero.

IEEE 754 based data types (FLOAT, DOUBLE PRECISION) round to the closest
even number.

On Thu, Jun 1, 2017 at 6:26 PM, Louis Battuello <louis(dot)battuello(at)etasseo(dot)com
> wrote:

> Is the round() function implemented differently for double precision than
> for numeric? Forgive me if this exists somewhere in the documentation, but
> I can't seem to find it.
>
> I've noticed with 9.6 on OSX, the .5 rounding is handled differently
> between the types. (I haven't tested other versions, yet.) For double
> precision values, even whole numbers are rounded down, yet for odds they
> are rounded up. For numeric values, all .5 numbers are rounded up.
>
> psql (9.6.3)
> Type "help" for help.
>
> postgres=# \x
> Expanded display is on.
> postgres=# select round(cast(1230.5 as double precision)) as
> round_double_even_0
> postgres-# ,round(cast(1231.5 as double precision)) as
> round_double_odd_1
> postgres-# ,round(cast(1232.5 as double precision)) as
> round_double_even_2
> postgres-# ,round(cast(1233.5 as double precision)) as
> round_double_odd_3
> postgres-# ,round(cast(1234.5 as double precision)) as
> round_double_even_4
> postgres-# ,round(cast(1235.5 as double precision)) as
> round_double_odd_5
> postgres-# ;
>
> -[ RECORD 1 ]-------+-----
> round_double_even_0 | 1230
> round_double_odd_1 | 1232
> round_double_even_2 | 1232
> round_double_odd_3 | 1234
> round_double_even_4 | 1234
> round_double_odd_5 | 1236
>
> postgres=# select round(cast(1230.5 as numeric)) as round_numeric_even_0
> postgres-# ,round(cast(1231.5 as numeric)) as round_numeric_odd_1
> postgres-# ,round(cast(1232.5 as numeric)) as round_numeric_even_2
> postgres-# ,round(cast(1233.5 as numeric)) as round_numeric_odd_3
> postgres-# ,round(cast(1234.5 as numeric)) as round_numeric_even_4
> postgres-# ,round(cast(1235.5 as numeric)) as round_numeric_odd_5
> postgres-# ;
>
> -[ RECORD 1 ]--------+-----
> round_numeric_even_0 | 1231
> round_numeric_odd_1 | 1232
> round_numeric_even_2 | 1233
> round_numeric_odd_3 | 1234
> round_numeric_even_4 | 1235
> round_numeric_odd_5 | 1236
>
> postgres=# select round(1230.5) as round_even_0
> ,round(1231.5) as round_odd_1
> ,round(1232.5) as round_even_2
> ,round(1233.5) as round_odd_3
> ,round(1234.5) as round_even_4
> ,round(1235.5) as round_odd_5
> ;
>
> -[ RECORD 1 ]+-----
> round_even_0 | 1231
> round_odd_1 | 1232
> round_even_2 | 1233
> round_odd_3 | 1234
> round_even_4 | 1235
> round_odd_5 | 1236
>
> postgres=# \q
>
> Why does the algorithm vary by data type?
>
> Or is something entirely different happening?
>
>