Re: Rounding Double Precision or Numeric

[Torsten Förtsch <tfoertsch123@xxxxxxxxx>]

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.battuello@xxxxxxxxxxx> 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?