Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit5e7c3d9

Browse files
committed
Add documentation and regression tests concerning rounding of numerics.
Michael Paquier, reviewed by Fabien Coelho
1 parent8eb6407 commit5e7c3d9

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::int2AS 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::int4AS 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::int8AS 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
DROPTABLE ceil_floor_round;
669669

670+
-- Check rounding, it should round ties away from zero.
671+
SELECT ias 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)
[8]ページ先頭
©2009-2025 Movatter.jp