Skip to content

Commit 5e7c3d9

Browse files
tglsfdctglsfdc
committed
Add documentation and regression tests concerning rounding of numerics.
Michael Paquier, reviewed by Fabien Coelho
1 parent 8eb6407 commit 5e7c3d9

File tree

10 files changed

+169
-0
lines changed

10 files changed

+169
-0
lines changed

‎doc/src/sgml/datatype.sgml

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -612,6 +612,31 @@ NUMERIC
612612
equivalent. Both types are part of the <acronym>SQL</acronym>
613613
standard.
614614
</para>
615+
616+
<para>
617+
When rounding values, the <type>numeric</type> type rounds ties away
618+
from zero, while (on most machines) the <type>real</type>
619+
and <type>double precision</type> types round ties to the nearest even
620+
number. For example:
621+
622+
<programlisting>
623+
SELECT x,
624+
round(x::numeric) AS num_round,
625+
round(x::double precision) AS dbl_round
626+
FROM generate_series(-3.5, 3.5, 1) as x;
627+
x | num_round | dbl_round
628+
------+-----------+-----------
629+
-3.5 | -4 | -4
630+
-2.5 | -3 | -2
631+
-1.5 | -2 | -2
632+
-0.5 | -1 | -0
633+
0.5 | 1 | 0
634+
1.5 | 2 | 2
635+
2.5 | 3 | 2
636+
3.5 | 4 | 4
637+
(8 rows)
638+
</programlisting>
639+
</para>
615640
</sect2>
616641

617642

‎src/test/regress/expected/int2.out

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -286,3 +286,23 @@ FROM (VALUES (-2.5::float8),
286286
2.5 | 2
287287
(7 rows)
288288

289+
-- check rounding when casting from numeric
290+
SELECT x, x::int2 AS int2_value
291+
FROM (VALUES (-2.5::numeric),
292+
(-1.5::numeric),
293+
(-0.5::numeric),
294+
(0.0::numeric),
295+
(0.5::numeric),
296+
(1.5::numeric),
297+
(2.5::numeric)) t(x);
298+
x | int2_value
299+
------+------------
300+
-2.5 | -3
301+
-1.5 | -2
302+
-0.5 | -1
303+
0.0 | 0
304+
0.5 | 1
305+
1.5 | 2
306+
2.5 | 3
307+
(7 rows)
308+

‎src/test/regress/expected/int4.out

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -383,3 +383,23 @@ FROM (VALUES (-2.5::float8),
383383
2.5 | 2
384384
(7 rows)
385385

386+
-- check rounding when casting from numeric
387+
SELECT x, x::int4 AS int4_value
388+
FROM (VALUES (-2.5::numeric),
389+
(-1.5::numeric),
390+
(-0.5::numeric),
391+
(0.0::numeric),
392+
(0.5::numeric),
393+
(1.5::numeric),
394+
(2.5::numeric)) t(x);
395+
x | int4_value
396+
------+------------
397+
-2.5 | -3
398+
-1.5 | -2
399+
-0.5 | -1
400+
0.0 | 0
401+
0.5 | 1
402+
1.5 | 2
403+
2.5 | 3
404+
(7 rows)
405+

‎src/test/regress/expected/int8-exp-three-digits.out

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -866,3 +866,23 @@ FROM (VALUES (-2.5::float8),
866866
2.5 | 2
867867
(7 rows)
868868

869+
-- check rounding when casting from numeric
870+
SELECT x, x::int8 AS int8_value
871+
FROM (VALUES (-2.5::numeric),
872+
(-1.5::numeric),
873+
(-0.5::numeric),
874+
(0.0::numeric),
875+
(0.5::numeric),
876+
(1.5::numeric),
877+
(2.5::numeric)) t(x);
878+
x | int8_value
879+
------+------------
880+
-2.5 | -3
881+
-1.5 | -2
882+
-0.5 | -1
883+
0.0 | 0
884+
0.5 | 1
885+
1.5 | 2
886+
2.5 | 3
887+
(7 rows)
888+

‎src/test/regress/expected/int8.out

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -866,3 +866,23 @@ FROM (VALUES (-2.5::float8),
866866
2.5 | 2
867867
(7 rows)
868868

869+
-- check rounding when casting from numeric
870+
SELECT x, x::int8 AS int8_value
871+
FROM (VALUES (-2.5::numeric),
872+
(-1.5::numeric),
873+
(-0.5::numeric),
874+
(0.0::numeric),
875+
(0.5::numeric),
876+
(1.5::numeric),
877+
(2.5::numeric)) t(x);
878+
x | int8_value
879+
------+------------
880+
-2.5 | -3
881+
-1.5 | -2
882+
-0.5 | -1
883+
0.0 | 0
884+
0.5 | 1
885+
1.5 | 2
886+
2.5 | 3
887+
(7 rows)
888+

‎src/test/regress/expected/numeric.out

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -730,6 +730,30 @@ SELECT a, ceil(a), ceiling(a), floor(a), round(a) FROM ceil_floor_round;
730730
(7 rows)
731731

732732
DROP TABLE ceil_floor_round;
733+
-- Check rounding, it should round ties away from zero.
734+
SELECT i as pow,
735+
round((-2.5 * 10 ^ i)::numeric, -i),
736+
round((-1.5 * 10 ^ i)::numeric, -i),
737+
round((-0.5 * 10 ^ i)::numeric, -i),
738+
round((0.5 * 10 ^ i)::numeric, -i),
739+
round((1.5 * 10 ^ i)::numeric, -i),
740+
round((2.5 * 10 ^ i)::numeric, -i)
741+
FROM generate_series(-5,5) AS t(i);
742+
pow | round | round | round | round | round | round
743+
-----+----------+----------+----------+---------+---------+---------
744+
-5 | -0.00003 | -0.00002 | -0.00001 | 0.00001 | 0.00002 | 0.00003
745+
-4 | -0.0003 | -0.0002 | -0.0001 | 0.0001 | 0.0002 | 0.0003
746+
-3 | -0.003 | -0.002 | -0.001 | 0.001 | 0.002 | 0.003
747+
-2 | -0.03 | -0.02 | -0.01 | 0.01 | 0.02 | 0.03
748+
-1 | -0.3 | -0.2 | -0.1 | 0.1 | 0.2 | 0.3
749+
0 | -3 | -2 | -1 | 1 | 2 | 3
750+
1 | -30 | -20 | -10 | 10 | 20 | 30
751+
2 | -300 | -200 | -100 | 100 | 200 | 300
752+
3 | -3000 | -2000 | -1000 | 1000 | 2000 | 3000
753+
4 | -30000 | -20000 | -10000 | 10000 | 20000 | 30000
754+
5 | -300000 | -200000 | -100000 | 100000 | 200000 | 300000
755+
(11 rows)
756+
733757
-- Testing for width_bucket(). For convenience, we test both the
734758
-- numeric and float8 versions of the function in this file.
735759
-- errors

‎src/test/regress/sql/int2.sql

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -102,3 +102,13 @@ FROM (VALUES (-2.5::float8),
102102
(0.5::float8),
103103
(1.5::float8),
104104
(2.5::float8)) t(x);
105+
106+
-- check rounding when casting from numeric
107+
SELECT x, x::int2 AS int2_value
108+
FROM (VALUES (-2.5::numeric),
109+
(-1.5::numeric),
110+
(-0.5::numeric),
111+
(0.0::numeric),
112+
(0.5::numeric),
113+
(1.5::numeric),
114+
(2.5::numeric)) t(x);

‎src/test/regress/sql/int4.sql

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -145,3 +145,13 @@ FROM (VALUES (-2.5::float8),
145145
(0.5::float8),
146146
(1.5::float8),
147147
(2.5::float8)) t(x);
148+
149+
-- check rounding when casting from numeric
150+
SELECT x, x::int4 AS int4_value
151+
FROM (VALUES (-2.5::numeric),
152+
(-1.5::numeric),
153+
(-0.5::numeric),
154+
(0.0::numeric),
155+
(0.5::numeric),
156+
(1.5::numeric),
157+
(2.5::numeric)) t(x);

‎src/test/regress/sql/int8.sql

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -215,3 +215,13 @@ FROM (VALUES (-2.5::float8),
215215
(0.5::float8),
216216
(1.5::float8),
217217
(2.5::float8)) t(x);
218+
219+
-- check rounding when casting from numeric
220+
SELECT x, x::int8 AS int8_value
221+
FROM (VALUES (-2.5::numeric),
222+
(-1.5::numeric),
223+
(-0.5::numeric),
224+
(0.0::numeric),
225+
(0.5::numeric),
226+
(1.5::numeric),
227+
(2.5::numeric)) t(x);

‎src/test/regress/sql/numeric.sql

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -667,6 +667,16 @@ INSERT INTO ceil_floor_round VALUES ('-0.000001');
667667
SELECT a, ceil(a), ceiling(a), floor(a), round(a) FROM ceil_floor_round;
668668
DROP TABLE ceil_floor_round;
669669

670+
-- Check rounding, it should round ties away from zero.
671+
SELECT i as pow,
672+
round((-2.5 * 10 ^ i)::numeric, -i),
673+
round((-1.5 * 10 ^ i)::numeric, -i),
674+
round((-0.5 * 10 ^ i)::numeric, -i),
675+
round((0.5 * 10 ^ i)::numeric, -i),
676+
round((1.5 * 10 ^ i)::numeric, -i),
677+
round((2.5 * 10 ^ i)::numeric, -i)
678+
FROM generate_series(-5,5) AS t(i);
679+
670680
-- Testing for width_bucket(). For convenience, we test both the
671681
-- numeric and float8 versions of the function in this file.
672682

0 commit comments

Comments
 (0)