Thread: any plans to support more rounding methods in sql?
hi,
i just needed to round some numbers down to 4 decimal places but a quick search
indicated that postgresql doesn't support all of the rounding methods so i had
to write this dreadful function:
create or replace function round_down_to_4_decimal_places(amount decimal(12,6))
returns decimal(10,4) stable language sql as $$
select
case
when $1 >= 0 then
case when $1 - round($1, 4) < 0 then round($1, 4) - 0.0001 else round($1, 4) end
else
case when $1 - round($1, 4) > 0 then round($1, 4) + 0.0001 else round($1, 4) end
end
$$;
this is fine for my purposes but it's not generic to different numbers of decimal
places and it's 26 times slower than the built-in round(v numeric, s int).
strangely, a plpgsql version is much faster but it's still 11 times slower than
a built-in version would be.
python's decimal module supports the following rounding methods:
ROUND_UP (round away from zero)
ROUND_DOWN (round towards zero)
ROUND_CEILING (round up)
ROUND_FLOOR (round down)
ROUND_HALF_UP (round 5 away from zero, rest to nearest)
ROUND_05UP (round away from zero if last significant digit is 0 or 5, rest towards zero)
ROUND_HALF_DOWN (round 5 towards zero, rest to nearest)
ROUND_HALF_EVEN (round 5 to even, rest to nearest)
are there any plans to support any other rounding methods natively?
cheers,
raf
Hello 2012/1/25 raf <raf@raf.org>: > hi, > > i just needed to round some numbers down to 4 decimal places but a quick search > indicated that postgresql doesn't support all of the rounding methods so i had > to write this dreadful function: > > create or replace function round_down_to_4_decimal_places(amount decimal(12,6)) > returns decimal(10,4) stable language sql as $$ > > select > case > when $1 >= 0 then > case when $1 - round($1, 4) < 0 then round($1, 4) - 0.0001 else round($1, 4) end > else > case when $1 - round($1, 4) > 0 then round($1, 4) + 0.0001 else round($1, 4) end > end > > $$; > > this is fine for my purposes but it's not generic to different numbers of decimal > places and it's 26 times slower than the built-in round(v numeric, s int). > strangely, a plpgsql version is much faster but it's still 11 times slower than > a built-in version would be. > > python's decimal module supports the following rounding methods: > > ROUND_UP (round away from zero) > ROUND_DOWN (round towards zero) > ROUND_CEILING (round up) > ROUND_FLOOR (round down) > ROUND_HALF_UP (round 5 away from zero, rest to nearest) > ROUND_05UP (round away from zero if last significant digit is 0 or 5, rest towards zero) > ROUND_HALF_DOWN (round 5 towards zero, rest to nearest) > ROUND_HALF_EVEN (round 5 to even, rest to nearest) > > are there any plans to support any other rounding methods natively? numeric operations are not usual use case for relation databases. For almost all users this complex set of functions should be contra productive. In PostgreSQL you can use a PLPythonu functionality or if you need it, then you can write own fast implementation in C. Regards Pavel Stehule > > cheers, > raf > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
On 25 January 2012 05:41, Pavel Stehule <pavel.stehule@gmail.com> wrote: > Hello > > 2012/1/25 raf <raf@raf.org>: >> hi, >> >> i just needed to round some numbers down to 4 decimal places but a quick search >> indicated that postgresql doesn't support all of the rounding methods so i had >> to write this dreadful function: Are you talking about always rounding down to the lower smallest-possible-increment, rather than following standard rules for rounding? That isn't such an esoteric use case - I believe that financial regulations in some jurisdictions require just that when calculating interest, for example. If you require exactly 4 digits of precision, it's possible to use this syntax: NUMERIC(precision, scale) That isn't going to affect the rounding behaviour though. -- Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 > i just needed to round some numbers down to 4 decimal places but a quick search > indicated that postgresql doesn't support all of the rounding methods so i had > to write this dreadful function: > > create or replace function round_down_to_4_decimal_places(amount decimal(12,6)) > returns decimal(10,4) stable language sql as $$ ... That should be IMMUTABLE not STABLE. > this is fine for my purposes but it's not generic to different numbers of decimal > places and it's 26 times slower than the built-in round(v numeric, s int). > strangely, a plpgsql version is much faster but it's still 11 times slower than > a built-in version would be. ... Yes, but is that speed really noticeable, or is it just a rounding error (ha ha ha!) compared to the total time of a query that uses these? > are there any plans to support any other rounding methods natively? Probably not; it would be up to you to champion them here first. First thing would be to demonstrate a clear use case. That may be hard, as I have no heard of anyone else needing these, and rare features that can be handled by extensions, user written functions, etc. are not likely to be added to core. But who knows, there may be a valid use case. First thing I would do is try a wrapper to a Python or Perl function, mark it IMMUTABLE, and see just how slow it really is. - -- Greg Sabino Mullane greg@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201201251316 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAk8gRzEACgkQvJuQZxSWSsiFzgCfQdA/3sb8Y5eL6z59pR8EZQq7 ioYAoMjKXhrFlHjfSq/eSbvhKa1y+F+u =swla -----END PGP SIGNATURE-----