On Mon, Mar 30, 2015 at 4:51 AM, James Cloos <cl...@jhcloos.com> wrote:
>>>>>> "MP" == Michael Paquier <michael.paqu...@gmail.com> writes:
>
> MP> So, attached is a patch that does 1) and 2) to make clear to the
> MP> user how numeric and double precision behave regarding rounding.
> MP> I am adding it to CF 2015-06 to keep track of it...
>
> Given that the examples show -2.5 rounds to -3, the IEEE term is
> roundTiesToAway, and the typical conversational english is round ties
> away from zero.
Ah, thanks for the correct wording. Fixed in the attached.
> RoundUp means mean towards +Infinity.
>
> 754 specifies that for decimal, either roundTiesToEven or roundTiesToAway
> are acceptable defaults, and which of the two applies is language dependent.
> Does ANSI SQL say anything about how numeric should round?
>
> In general, for decimals (or anything other than binary), there are
> twelve possible roundings:
>
> ToEven ToOdd AwayFromZero ToZero Up Down
> TiesToEven TiesToOdd TiesAwayFromZero TiesToZero TiesUp TiesDown
>
> (Up is the same as ceil(3), Down as floor(3).)
Well, I am not sure about that... But reading this thread changing the
default rounding sounds unwelcome. So it may be better to just put in
words the rounding method used now in the docs, with perhaps a mention
that this is not completely in-line with the SQL spec if that's not
the case.
--
Michael
From ae28d91519854e6d47d2c864fa26b65c70bb0526 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 | 19 +++++++++++++++++++
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, 143 insertions(+)
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index da1f25f..eb131c3 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -612,6 +612,25 @@ 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 ties away from zero, and the <type>double precision</type>
+ type rounds ties away to 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..e6ee548 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 rounding, it should round ties away from zero.
+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..982287c 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 rounding, it should round ties away from zero.
+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
