On Sun, Mar 29, 2015 at 7:59 AM, Michael Paquier
<michael.paqu...@gmail.com> wrote:
> On Sun, Mar 29, 2015 at 5:34 AM, Gavin Flower
> <gavinflo...@archidevsys.co.nz> wrote:
>> On 28/03/15 21:58, Dean Rasheed wrote:
>> [...]
>>>
>>>
>>> Andrew mentioned that there have been complaints from people doing
>>> calculations with monetary data that we don't implement
>>> round-to-nearest-even (Banker's) rounding. It's actually the case that
>>> various different financial calculations demand different specific
>>> rounding modes, so it wouldn't be enough to simply change the default
>>> - we would have to provide a choice of modes.
>>
>> [...]
>>
>> Could the 2 current round functions have cousins that included an extra char
>> parameter (or string), that indicated the type of rounding?
>>
>> So we don't end up with an explosion of rounding functions, yet could cope
>> with a limited set of additional rounding modes initially, and possibly
>> others in the future.
>
> Instead of extending round, isn't what we are looking at here a new
> data type? I have doubts that we only want to have a way to switch
> round() between different modes. Hence, what we could do is:
> 1) Mention in the docs that numeric does round-half-away-from-zero
> 2) Add regression tests for numeric(n,m) and round(numeric)
> 3) Add a TODO item for something like numeric2, doing rounding-at-even
> (this could be an extension as well), but with the number of
> duplication that it may have with numeric, an in-core type would make
> sense, to facilitate things exposing some of structures key structures
> would help.
So, attached is a patch that does 1) and 2) to make clear to the user
how numeric and double precision behave regarding rounding. I am
adding it to CF 2015-06 to keep track of it...
--
Michael
From 21e2da3d8c480f28c2cb469a004dbc225a522725 Mon Sep 17 00:00:00 2001
From: Michael Paquier <michael@otacoo.com>
Date: Sun, 29 Mar 2015 19:46:50 +0900
Subject: [PATCH] Precise rounding behavior of numeric and double precision in
docs
Regression tests improving the coverage in this area are added as well.
---
doc/src/sgml/datatype.sgml | 18 ++++++++++++++++++
src/test/regress/expected/int2.out | 20 ++++++++++++++++++++
src/test/regress/expected/int4.out | 20 ++++++++++++++++++++
src/test/regress/expected/int8.out | 20 ++++++++++++++++++++
src/test/regress/expected/numeric.out | 24 ++++++++++++++++++++++++
src/test/regress/sql/int2.sql | 10 ++++++++++
src/test/regress/sql/int4.sql | 10 ++++++++++
src/test/regress/sql/int8.sql | 10 ++++++++++
src/test/regress/sql/numeric.sql | 10 ++++++++++
9 files changed, 142 insertions(+)
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index da1f25f..0342c8a 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -612,6 +612,24 @@ NUMERIC
equivalent. Both types are part of the <acronym>SQL</acronym>
standard.
</para>
+
+ <para>
+ With using the <function>round</> function, the <type>numeric</type>
+ type rounds half-up, and the <type>double precision</> type half-even.
+
+<programlisting>
+SELECT round(1.5::numeric), round(2.5::numeric);
+ round | round
+-------+-------
+ 2 | 3
+(1 row)
+SELECT round(1.5::double precision), round(2.5::double precision);
+ round | round
+-------+-------
+ 2 | 2
+(1 row)
+</programlisting>
+ </para>
</sect2>
diff --git a/src/test/regress/expected/int2.out b/src/test/regress/expected/int2.out
index 311fe73..3ea4ed9 100644
--- a/src/test/regress/expected/int2.out
+++ b/src/test/regress/expected/int2.out
@@ -286,3 +286,23 @@ FROM (VALUES (-2.5::float8),
2.5 | 2
(7 rows)
+-- check rounding when casting from numeric
+SELECT x, x::int2 AS int2_value
+FROM (VALUES (-2.5::numeric),
+ (-1.5::numeric),
+ (-0.5::numeric),
+ (0.0::numeric),
+ (0.5::numeric),
+ (1.5::numeric),
+ (2.5::numeric)) t(x);
+ x | int2_value
+------+------------
+ -2.5 | -3
+ -1.5 | -2
+ -0.5 | -1
+ 0.0 | 0
+ 0.5 | 1
+ 1.5 | 2
+ 2.5 | 3
+(7 rows)
+
diff --git a/src/test/regress/expected/int4.out b/src/test/regress/expected/int4.out
index 83fe022..372fd4d 100644
--- a/src/test/regress/expected/int4.out
+++ b/src/test/regress/expected/int4.out
@@ -383,3 +383,23 @@ FROM (VALUES (-2.5::float8),
2.5 | 2
(7 rows)
+-- check rounding when casting from numeric
+SELECT x, x::int4 AS int4_value
+FROM (VALUES (-2.5::numeric),
+ (-1.5::numeric),
+ (-0.5::numeric),
+ (0.0::numeric),
+ (0.5::numeric),
+ (1.5::numeric),
+ (2.5::numeric)) t(x);
+ x | int4_value
+------+------------
+ -2.5 | -3
+ -1.5 | -2
+ -0.5 | -1
+ 0.0 | 0
+ 0.5 | 1
+ 1.5 | 2
+ 2.5 | 3
+(7 rows)
+
diff --git a/src/test/regress/expected/int8.out b/src/test/regress/expected/int8.out
index da8be51..ed0bd34 100644
--- a/src/test/regress/expected/int8.out
+++ b/src/test/regress/expected/int8.out
@@ -866,3 +866,23 @@ FROM (VALUES (-2.5::float8),
2.5 | 2
(7 rows)
+-- check rounding when casting from numeric
+SELECT x, x::int8 AS int8_value
+FROM (VALUES (-2.5::numeric),
+ (-1.5::numeric),
+ (-0.5::numeric),
+ (0.0::numeric),
+ (0.5::numeric),
+ (1.5::numeric),
+ (2.5::numeric)) t(x);
+ x | int8_value
+------+------------
+ -2.5 | -3
+ -1.5 | -2
+ -0.5 | -1
+ 0.0 | 0
+ 0.5 | 1
+ 1.5 | 2
+ 2.5 | 3
+(7 rows)
+
diff --git a/src/test/regress/expected/numeric.out b/src/test/regress/expected/numeric.out
index 9d68145..fa95cae 100644
--- a/src/test/regress/expected/numeric.out
+++ b/src/test/regress/expected/numeric.out
@@ -730,6 +730,30 @@ SELECT a, ceil(a), ceiling(a), floor(a), round(a) FROM ceil_floor_round;
(7 rows)
DROP TABLE ceil_floor_round;
+-- Check half-up rounding
+SELECT i as pow,
+ round((-2.5 * 10 ^ i)::numeric, -i),
+ round((-1.5 * 10 ^ i)::numeric, -i),
+ round((-0.5 * 10 ^ i)::numeric, -i),
+ round((0.5 * 10 ^ i)::numeric, -i),
+ round((1.5 * 10 ^ i)::numeric, -i),
+ round((2.5 * 10 ^ i)::numeric, -i)
+FROM generate_series(-5,5) AS t(i);
+ pow | round | round | round | round | round | round
+-----+----------+----------+----------+---------+---------+---------
+ -5 | -0.00003 | -0.00002 | -0.00001 | 0.00001 | 0.00002 | 0.00003
+ -4 | -0.0003 | -0.0002 | -0.0001 | 0.0001 | 0.0002 | 0.0003
+ -3 | -0.003 | -0.002 | -0.001 | 0.001 | 0.002 | 0.003
+ -2 | -0.03 | -0.02 | -0.01 | 0.01 | 0.02 | 0.03
+ -1 | -0.3 | -0.2 | -0.1 | 0.1 | 0.2 | 0.3
+ 0 | -3 | -2 | -1 | 1 | 2 | 3
+ 1 | -30 | -20 | -10 | 10 | 20 | 30
+ 2 | -300 | -200 | -100 | 100 | 200 | 300
+ 3 | -3000 | -2000 | -1000 | 1000 | 2000 | 3000
+ 4 | -30000 | -20000 | -10000 | 10000 | 20000 | 30000
+ 5 | -300000 | -200000 | -100000 | 100000 | 200000 | 300000
+(11 rows)
+
-- Testing for width_bucket(). For convenience, we test both the
-- numeric and float8 versions of the function in this file.
-- errors
diff --git a/src/test/regress/sql/int2.sql b/src/test/regress/sql/int2.sql
index 5e9774e..7dbafb6 100644
--- a/src/test/regress/sql/int2.sql
+++ b/src/test/regress/sql/int2.sql
@@ -102,3 +102,13 @@ FROM (VALUES (-2.5::float8),
(0.5::float8),
(1.5::float8),
(2.5::float8)) t(x);
+
+-- check rounding when casting from numeric
+SELECT x, x::int2 AS int2_value
+FROM (VALUES (-2.5::numeric),
+ (-1.5::numeric),
+ (-0.5::numeric),
+ (0.0::numeric),
+ (0.5::numeric),
+ (1.5::numeric),
+ (2.5::numeric)) t(x);
diff --git a/src/test/regress/sql/int4.sql b/src/test/regress/sql/int4.sql
index d188140..f014cb2 100644
--- a/src/test/regress/sql/int4.sql
+++ b/src/test/regress/sql/int4.sql
@@ -145,3 +145,13 @@ FROM (VALUES (-2.5::float8),
(0.5::float8),
(1.5::float8),
(2.5::float8)) t(x);
+
+-- check rounding when casting from numeric
+SELECT x, x::int4 AS int4_value
+FROM (VALUES (-2.5::numeric),
+ (-1.5::numeric),
+ (-0.5::numeric),
+ (0.0::numeric),
+ (0.5::numeric),
+ (1.5::numeric),
+ (2.5::numeric)) t(x);
diff --git a/src/test/regress/sql/int8.sql b/src/test/regress/sql/int8.sql
index 6972375..e890452 100644
--- a/src/test/regress/sql/int8.sql
+++ b/src/test/regress/sql/int8.sql
@@ -215,3 +215,13 @@ FROM (VALUES (-2.5::float8),
(0.5::float8),
(1.5::float8),
(2.5::float8)) t(x);
+
+-- check rounding when casting from numeric
+SELECT x, x::int8 AS int8_value
+FROM (VALUES (-2.5::numeric),
+ (-1.5::numeric),
+ (-0.5::numeric),
+ (0.0::numeric),
+ (0.5::numeric),
+ (1.5::numeric),
+ (2.5::numeric)) t(x);
diff --git a/src/test/regress/sql/numeric.sql b/src/test/regress/sql/numeric.sql
index 1633e4c..7e615e6 100644
--- a/src/test/regress/sql/numeric.sql
+++ b/src/test/regress/sql/numeric.sql
@@ -667,6 +667,16 @@ INSERT INTO ceil_floor_round VALUES ('-0.000001');
SELECT a, ceil(a), ceiling(a), floor(a), round(a) FROM ceil_floor_round;
DROP TABLE ceil_floor_round;
+-- Check half-up rounding
+SELECT i as pow,
+ round((-2.5 * 10 ^ i)::numeric, -i),
+ round((-1.5 * 10 ^ i)::numeric, -i),
+ round((-0.5 * 10 ^ i)::numeric, -i),
+ round((0.5 * 10 ^ i)::numeric, -i),
+ round((1.5 * 10 ^ i)::numeric, -i),
+ round((2.5 * 10 ^ i)::numeric, -i)
+FROM generate_series(-5,5) AS t(i);
+
-- Testing for width_bucket(). For convenience, we test both the
-- numeric and float8 versions of the function in this file.
--
2.3.4
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
