Rounding to even for numeric data type
Hi all,
A couple of days ago a bug has showed up regarding rounding of float here:
/messages/by-id/20150320194337.2573.72944@wrigleys.postgresql.org
The result being that the version of rint() shipped in src/port was
not IEEE compliant when rounding to even (MSVC < 2013 at least using
it), leading to inconsistent results depending on if the platform uses
src/port's rint() or the platform's one.
During this thread, Tom has raised as well that rounding for numeric
is not that IEEE-compliant:
/messages/by-id/22366.1427313454@sss.pgh.pa.us
For example:
=# SELECT round(2.5::numeric), round(1.5::numeric),
round(0.5::numeric), round(-2.5::numeric);
round | round | round | round
-------+-------+-------+-------
3 | 2 | 1 | -3
(1 row)
=# SELECT round(2.5::float), round(1.5::float), round(0.5::float),
round(-2.5::float);
round | round | round | round
-------+-------+-------+-------
2 | 2 | 0 | -2
(1 row)
It sounds appealing to switch the default behavior to something that
is more IEEE-compliant, and not only for scale == 0. Now one can argue
as well that changing the default is risky for existing applications,
or the other way around that other RDBMs (?) are more compliant than
us for their equivalent numeric data type, and people get confused
when switching to Postgres.
An idea, from Dean, would be to have a new specific version for
round() able to do compliant IEEE rounding to even as well...
Opinions?
Regards,
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Michael Paquier <michael.paquier@gmail.com> writes:
It sounds appealing to switch the default behavior to something that
is more IEEE-compliant, and not only for scale == 0. Now one can argue
as well that changing the default is risky for existing applications,
or the other way around that other RDBMs (?) are more compliant than
us for their equivalent numeric data type, and people get confused
when switching to Postgres.
An idea, from Dean, would be to have a new specific version for
round() able to do compliant IEEE rounding to even as well...
I think confining the change to round() would be a fundamental error.
The main reason why round-to-nearest-even is IEEE standard is that it
reduces error accumulation over long chains of calculations, such as
in numeric's power and trig functions; if we go to the trouble of
implementing such a behavior, we certainly want to use it there.
I think the concern over backwards compatibility here is probably
overblown; but if we're sufficiently worried about it, a possible
compromise is to invent a numeric_rounding_mode GUC, so that people
could get back the old behavior if they really care.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
"Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:
Tom> I think the concern over backwards compatibility here is probably
Tom> overblown; but if we're sufficiently worried about it, a possible
Tom> compromise is to invent a numeric_rounding_mode GUC, so that
Tom> people could get back the old behavior if they really care.
I only see one issue with this, but it's a nasty one: do we really want
to make all numeric operations that might do rounding stable rather than
immutable?
--
Andrew (irc:RhodiumToad)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 27 March 2015 at 23:26, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Michael Paquier <michael.paquier@gmail.com> writes:
It sounds appealing to switch the default behavior to something that
is more IEEE-compliant, and not only for scale == 0. Now one can argue
as well that changing the default is risky for existing applications,
or the other way around that other RDBMs (?) are more compliant than
us for their equivalent numeric data type, and people get confused
when switching to Postgres.An idea, from Dean, would be to have a new specific version for
round() able to do compliant IEEE rounding to even as well...I think confining the change to round() would be a fundamental error.
The main reason why round-to-nearest-even is IEEE standard is that it
reduces error accumulation over long chains of calculations, such as
in numeric's power and trig functions; if we go to the trouble of
implementing such a behavior, we certainly want to use it there.
Sure, using round-to-nearest-even for intermediate rounding in complex
numeric methods would be a good way to reduce (but not completely
eliminate) rounding errors. But that's a somewhat different
proposition from changing the default for round(), which is a much
more user-visible change. If we did implement a choice of rounding
modes, I would still argue for keeping round-half-away-from-zero as
the default mode for round().
I think the concern over backwards compatibility here is probably
overblown; but if we're sufficiently worried about it, a possible
compromise is to invent a numeric_rounding_mode GUC, so that people
could get back the old behavior if they really care.
Backwards compatibility is certainly one concern. Michael also
mentioned compatibility with other databases, and its worth noting
that Oracle, MySQL, DB2 and SQL Server all use the same default
round-half-away-from-zero "Schoolbook" rounding mode in round() for
their equivalents of numeric. Most of those other DBs are also careful
to document exactly how round() behaves. To make our round() function
do something different by default isn't going to make porting any
easier.
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. I also agree with Andrew
that all numeric functions should be kept immutable.
Regards,
Dean
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 28 March 2015 at 05:16, Andrew Gierth <andrew@tao11.riddles.org.uk> wrote:
"Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:
Tom> I think the concern over backwards compatibility here is probably
Tom> overblown; but if we're sufficiently worried about it, a possible
Tom> compromise is to invent a numeric_rounding_mode GUC, so that
Tom> people could get back the old behavior if they really care.I only see one issue with this, but it's a nasty one: do we really want
to make all numeric operations that might do rounding stable rather than
immutable?
Yeah, making all numeric functions non-immutable seems like a really bad idea.
Regards,
Dean
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Sat, Mar 28, 2015 at 5:58 PM, Dean Rasheed wrote:
On 27 March 2015 at 23:26, Tom Lane wrote:
I think the concern over backwards compatibility here is probably
overblown; but if we're sufficiently worried about it, a possible
compromise is to invent a numeric_rounding_mode GUC, so that people
could get back the old behavior if they really care.Backwards compatibility is certainly one concern. Michael also
mentioned compatibility with other databases, and its worth noting
that Oracle, MySQL, DB2 and SQL Server all use the same default
round-half-away-from-zero "Schoolbook" rounding mode in round() for
their equivalents of numeric. Most of those other DBs are also careful
to document exactly how round() behaves. To make our round() function
do something different by default isn't going to make porting any
easier.
I was not aware of that, and that's really an interesting point.
Thanks! It would indeed not be welcome for people migrating an
application to Postgres if we behave differently from the others.
Then, perhaps the solution would be to have this rounding GUC, but
pointing by default to round-half-away-from-zero and not round-to-even
as mentioned upthread already.
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. I also agree with Andrew
that all numeric functions should be kept immutable.
This looks like a plan. Honestly by reading this thread the thing that
IMO we should not do is closing ourselves into a single mode of
calculation.
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
Sure, using round-to-nearest-even for intermediate rounding in
complex numeric methods would be a good way to reduce (but not
completely eliminate) rounding errors. But that's a somewhat
different proposition from changing the default for round(),
which is a much more user-visible change. If we did implement a
choice of rounding modes, I would still argue for keeping
round-half-away-from-zero as the default mode for round().
I'm inclined to agree. In business software development, that's
how I've seen the "stakeholder" expectations. Thinking back, I can
remember dealing with rounding in manufacturing incentive pay
calculation, interfacing long-range demand forcasting to production
planning, interfacing engineers' CAD/CAM software to IBM MAPICS,
professionals' timekeeping/billing/AR systems, and various general
accounting software systems; and as I seem to remember those
efforts, round half away from zero has always been when end users
understood and expected when explicitly rounding a final result.
I understand how rounding half to even in intermediate results
minimizes rounding error, and would not be surprised to see some
users with different expectations, but there is clearly a large
base of people who would be surprised by it when rounding a final
result.
I also agree with Andrew that all numeric functions should be
kept immutable.
Which means no GUC should affect how it behaves, although a
function with a parameter to control rounding behavior would be OK.
It kinda seems like the SQL round() function should have a
parameter to control this which defaults to the historical behavior
when omitted.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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.
Cheers,
Gavin
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Sun, Mar 29, 2015 at 5:34 AM, Gavin Flower
<GavinFlower@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.
Regards,
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Sat, Mar 28, 2015 at 3:59 PM, Michael Paquier <michael.paquier@gmail.com>
wrote:
On Sun, Mar 29, 2015 at 5:34 AM, Gavin Flower
<GavinFlower@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, create a numeric type for each possible rounding mode? That implies
at least two types, round-half-even and round-half-away-from-zero, with
suitable abbreviations: numeric_rhe, numeric_raz.
If the goal is to make plain numeric IEEE standard conforming then giving
the user a way to switch all existing numeric types to numeric_raz would be
nice.
Implicit casts between each of the various numeric types would be needed
and understandable.
I'm pondering calling them numeric_eng and numeric_bus (for engineering and
business respectively)...
David J.
On 29/03/15 13:07, David G. Johnston wrote:
On Sat, Mar 28, 2015 at 3:59 PM, Michael Paquier
<michael.paquier@gmail.com <mailto:michael.paquier@gmail.com>>wrote:On Sun, Mar 29, 2015 at 5:34 AM, Gavin Flower
<GavinFlower@archidevsys.co.nz
<mailto:GavinFlower@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 thecase that
various different financial calculations demand different specific
rounding modes, so it wouldn't be enough to simply change thedefault
- 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, create a numeric type for each possible rounding mode? That
implies at least two types, round-half-even and
round-half-away-from-zero, with suitable abbreviations: numeric_rhe,
numeric_raz.If the goal is to make plain numeric IEEE standard conforming then
giving the user a way to switch all existing numeric types to
numeric_raz would be nice.Implicit casts between each of the various numeric types would be
needed and understandable.I'm pondering calling them numeric_eng and numeric_bus (for
engineering and business respectively)...David J.
In Java, there are 8 rounding modes specified:
https://docs.oracle.com/javase/8/docs/api/java/math/RoundingMode.html
Some of these may be relevant to pg.
Cheers,
Gavin
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Sun, Mar 29, 2015 at 9:21 AM, Gavin Flower
<GavinFlower@archidevsys.co.nz> wrote:
On 29/03/15 13:07, David G. Johnston wrote:
On Sat, Mar 28, 2015 at 3:59 PM, Michael Paquier
<michael.paquier@gmail.com <mailto:michael.paquier@gmail.com>>wrote:On Sun, Mar 29, 2015 at 5:34 AM, Gavin Flower
<GavinFlower@archidevsys.co.nz
<mailto:GavinFlower@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 thecase that
various different financial calculations demand different specific
rounding modes, so it wouldn't be enough to simply change thedefault
- 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, create a numeric type for each possible rounding mode? That implies at
least two types, round-half-even and round-half-away-from-zero, with
suitable abbreviations: numeric_rhe, numeric_raz.
The existing numeric now does half-up rounding.
If the goal is to make plain numeric IEEE standard conforming then giving
the user a way to switch all existing numeric types to numeric_raz would be
nice.Implicit casts between each of the various numeric types would be needed
and understandable.
That's exactly the thing I think would be helpful.
I'm pondering calling them numeric_eng and numeric_bus (for engineering
and business respectively)...In Java, there are 8 rounding modes specified:
https://docs.oracle.com/javase/8/docs/api/java/math/RoundingMode.html
Some of these may be relevant to pg.
That's interesting. I didn't recall those details.
Regards,
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Sun, Mar 29, 2015 at 7:59 AM, Michael Paquier
<michael.paquier@gmail.com> wrote:
On Sun, Mar 29, 2015 at 5:34 AM, Gavin Flower
<GavinFlower@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
Attachments:
0001-Precise-rounding-behavior-of-numeric-and-double-prec.patchapplication/x-patch; name=0001-Precise-rounding-behavior-of-numeric-and-double-prec.patchDownload
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
"MP" == Michael Paquier <michael.paquier@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.
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).)
-JimC
--
James Cloos <cloos@jhcloos.com> OpenPGP: 0x997A9F17ED7DAEA6
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Mon, Mar 30, 2015 at 4:51 AM, James Cloos <cloos@jhcloos.com> wrote:
"MP" == Michael Paquier <michael.paquier@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
Attachments:
0001-Precise-rounding-behavior-of-numeric-and-double-prec.patchapplication/x-patch; name=0001-Precise-rounding-behavior-of-numeric-and-double-prec.patchDownload
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
Michael Paquier wrote:
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.
The SQL standard does not care, it says that numbers and other data types
should, whenever necessary, be rounded or truncated in an implementation-
defined fashion.
I cannot find any mention of a round() function.
Yours,
Laurenz Albe
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Dean Rasheed wrote, On 2015-03-28 10:01:
On 28 March 2015 at 05:16, Andrew Gierth <andrew@tao11.riddles.org.uk> wrote:
"Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:
Tom> I think the concern over backwards compatibility here is probably
Tom> overblown; but if we're sufficiently worried about it, a possible
Tom> compromise is to invent a numeric_rounding_mode GUC, so that
Tom> people could get back the old behavior if they really care.I only see one issue with this, but it's a nasty one: do we really want
to make all numeric operations that might do rounding stable rather than
immutable?Yeah, making all numeric functions non-immutable seems like a really bad idea.
Would it be possible to make it an unchangeable per-cluster or
per-database setting, kinda like how encoding behaves? Wouldn't that
allow to keep the functions immutable?
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Wed, Apr 22, 2015 at 9:30 PM, Pedro Gimeno
<pgsql-004@personal.formauri.es> wrote:
Dean Rasheed wrote, On 2015-03-28 10:01:
On 28 March 2015 at 05:16, Andrew Gierth <andrew@tao11.riddles.org.uk> wrote:
"Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:
Tom> I think the concern over backwards compatibility here is probably
Tom> overblown; but if we're sufficiently worried about it, a possible
Tom> compromise is to invent a numeric_rounding_mode GUC, so that
Tom> people could get back the old behavior if they really care.I only see one issue with this, but it's a nasty one: do we really want
to make all numeric operations that might do rounding stable rather than
immutable?Yeah, making all numeric functions non-immutable seems like a really bad idea.
Would it be possible to make it an unchangeable per-cluster or
per-database setting, kinda like how encoding behaves? Wouldn't that
allow to keep the functions immutable?
Rounding is not something that can be enforced at the database or
server level but at data type level, see for example the differences
already present for double precision and numeric as mentioned
upthread. In short, you could keep rounding functions immutable by
having one data type with a different rounding method. At least that's
an idea.
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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...
Quick review: patches applies, make check is fine, all is well.
Two minor suggestions:
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.
For the documentation, I would also add 3.5 so that rounding to even is
even clearer:-)
--
Fabien.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
Attachments:
0001-Precise-rounding-behavior-of-numeric-and-double-prec.patchapplication/x-patch; name=0001-Precise-rounding-behavior-of-numeric-and-double-prec.patchDownload
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
v2 applied & tested.
[...] Not sure about that, the tests are placed here to be consistent
with for is done for float8.
Maybe float8 to numeric casts could have been in numeric too.
[...] I reworked the example in the docs.
Indeed, looks good.
--
Fabien.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Michael Paquier <michael.paquier@gmail.com> writes:
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.
Pushed with minor adjustments --- you missed updating
int8-exp-three-digits.out, and I thought the documentation wording
could be better.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers