Thread: [GENERAL] Rounding Double Precision or Numeric
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?
This is documented in section 8.1.2 in the manual. (https://www.postgresql.org/
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@etasseo.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=# \xExpanded display is on.postgres=# select round(cast(1230.5 as double precision)) as round_double_even_0postgres-# ,round(cast(1231.5 as double precision)) as round_double_odd_1postgres-# ,round(cast(1232.5 as double precision)) as round_double_even_2postgres-# ,round(cast(1233.5 as double precision)) as round_double_odd_3postgres-# ,round(cast(1234.5 as double precision)) as round_double_even_4postgres-# ,round(cast(1235.5 as double precision)) as round_double_odd_5postgres-# ;-[ RECORD 1 ]-------+-----round_double_even_0 | 1230round_double_odd_1 | 1232round_double_even_2 | 1232round_double_odd_3 | 1234round_double_even_4 | 1234round_double_odd_5 | 1236postgres=# select round(cast(1230.5 as numeric)) as round_numeric_even_0postgres-# ,round(cast(1231.5 as numeric)) as round_numeric_odd_1postgres-# ,round(cast(1232.5 as numeric)) as round_numeric_even_2postgres-# ,round(cast(1233.5 as numeric)) as round_numeric_odd_3postgres-# ,round(cast(1234.5 as numeric)) as round_numeric_even_4postgres-# ,round(cast(1235.5 as numeric)) as round_numeric_odd_5postgres-# ;-[ RECORD 1 ]--------+-----round_numeric_even_0 | 1231round_numeric_odd_1 | 1232round_numeric_even_2 | 1233round_numeric_odd_3 | 1234round_numeric_even_4 | 1235round_numeric_odd_5 | 1236postgres=# 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 | 1231round_odd_1 | 1232round_even_2 | 1233round_odd_3 | 1234round_even_4 | 1235round_odd_5 | 1236postgres=# \qWhy does the algorithm vary by data type?Or is something entirely different happening?
> On Jun 1, 2017, at 9:26 AM, Louis Battuello <louis.battuello@etasseo.com> wrote: > > Is the round() function implemented differently for double precision than for numeric? Forgive me if this exists somewherein the documentation, but I can't seem to find it. https://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL "When rounding values, the numeric type rounds ties away from zero, while (on most machines) the real and double precisiontypes round ties to the nearest even number.". > Why does the algorithm vary by data type? Just guessing, but I'd assume because the NUMERIC type behaves as required by the SQL spec, while float and double are vanillaIEEE754 arithmetic and will do whatever the underlying hardware is configured to do, usually round to nearest even. Cheers, Steve
On Thu, Jun 1, 2017 at 10:42 AM, Steve Atkins <steve@blighty.com> wrote: > >> On Jun 1, 2017, at 9:26 AM, Louis Battuello <louis.battuello@etasseo.com> wrote: >> >> Is the round() function implemented differently for double precision than for numeric? Forgive me if this exists somewherein the documentation, but I can't seem to find it. > > https://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL > > "When rounding values, the numeric type rounds ties away from zero, while (on most machines) the real and double precisiontypes round ties to the nearest even number.". > >> Why does the algorithm vary by data type? > > Just guessing, but I'd assume because the NUMERIC type behaves as required by the SQL spec, while float and double arevanilla IEEE754 arithmetic and will do whatever the underlying hardware is configured to do, usually round to nearesteven. > Exactly. It's important to remember that floats and doubles are imprecise representations meant for speed, while numeric is designed for accuracy not speed. If one needs an exact answer, one does not use floats.
On Jun 1, 2017, at 12:58 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:On Thu, Jun 1, 2017 at 10:42 AM, Steve Atkins <steve@blighty.com> wrote:On Jun 1, 2017, at 9:26 AM, Louis Battuello <louis.battuello@etasseo.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.
https://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL
"When rounding values, the numeric type rounds ties away from zero, while (on most machines) the real and double precision types round ties to the nearest even number.".Why does the algorithm vary by data type?
Just guessing, but I'd assume because the NUMERIC type behaves as required by the SQL spec, while float and double are vanilla IEEE754 arithmetic and will do whatever the underlying hardware is configured to do, usually round to nearest even.
Exactly. It's important to remember that floats and doubles are
imprecise representations meant for speed, while numeric is designed
for accuracy not speed.
If one needs an exact answer, one does not use floats.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Perfectly clear now. Thank you!