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/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@etasseo.com
Show quoted text
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 | 1236postgres=# 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 | 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 | 1231
round_odd_1 | 1232
round_even_2 | 1233
round_odd_3 | 1234
round_even_4 | 1235
round_odd_5 | 1236postgres=# \q
Why 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 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.
Cheers,
Steve
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
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 <mailto: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 <mailto:pgsql-general@postgresql.org>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general <http://www.postgresql.org/mailpref/pgsql-general>
Perfectly clear now. Thank you!