On Sat, May 2, 2015 at 9:53 PM, Fabien COELHO wrote:
> Quick review: patches applies, make check is fine, all is well.
Thanks for the feedback, Fabien!
> All the casting tests could be put in "numeric.sql", as there are all
> related to numeric and that would avoid duplicating the values lists.
Not sure about that, the tests are placed here to be consistent with
for is done for float8.
> For the documentation, I would also add 3.5 so that rounding to even is even
> clearer:-)
Good idea. I reworked the example in the docs.
--
Michael
From 7a40acab425f25f7c06344b2e039405542ed020e Mon Sep 17 00:00:00 2001
From: Michael Paquier <michael@otacoo.com>
Date: Sat, 9 May 2015 22:15:47 +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..24efe25 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 num,
+ round(num::double precision) AS prec_round,
+ round(num::numeric) AS nume_round
+ FROM generate_series(1.5, 3.5, 1) as num;
+ num | prec_round | nume_round
+-----+------------+------------
+ 1.5 | 2 | 2
+ 2.5 | 2 | 3
+ 3.5 | 4 | 4
+(3 rows)
+</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.4.0
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
