Which method does Postgres round(v numeric, s int)
use?
I'm looking for documentation reference.
INT is a 4-byte column storing integer values from -2147483648 to +2147483647. NUMERIC(9, 0) is a 9- to 14-byte column storing integer values from -999999999 to 999999999.
There are three kinds of integers in PostgreSQL: Small integer ( SMALLINT ) is 2-byte signed integer that has a range from -32,768 to 32,767. Integer ( INT ) is a 4-byte integer that has a range from -2,147,483,648 to 2,147,483,647.
It's not documented, so it can change.
Here are my round_half_even(numeric,integer)
:
create or replace function round_half_even(val numeric, prec integer)
returns numeric
as $$
declare
retval numeric;
difference numeric;
even boolean;
begin
retval := round(val,prec);
difference := retval-val;
if abs(difference)*(10::numeric^prec) = 0.5::numeric then
even := (retval * (10::numeric^prec)) % 2::numeric = 0::numeric;
if not even then
retval := round(val-difference,prec);
end if;
end if;
return retval;
end;
$$ language plpgsql immutable strict;
And round_half_odd(numeric,integer)
:
create or replace function round_half_odd(val numeric, prec integer)
returns numeric
as $$
declare
retval numeric;
difference numeric;
even boolean;
begin
retval := round(val,prec);
difference := retval-val;
if abs(difference)*(10::numeric^prec) = 0.5::numeric then
even := (retval * (10::numeric^prec)) % 2::numeric = 0::numeric;
if even then
retval := round(val-difference,prec);
end if;
end if;
return retval;
end;
$$ language plpgsql immutable strict;
They manage about 500000 invocations per second, 6 times slower than a standard round(numeric,integer)
. They also work for zero and for negative precision.
Sorry, I don't see any hint of this in the documentation, but a look at the code indicates it's using round half away from zero; the carry
is always added to digits
, thereby increasing the absolute value of the variable, regardless of what its sign
is. A simple experiment (psql 9.1) confirms this:
test=# CREATE TABLE nvals (v numeric(5,2));
CREATE TABLE
test=# INSERT INTO nvals (v) VALUES (-0.25), (-0.15), (-0.05), (0.05), (0.15), (0.25);
INSERT 0 6
test=# SELECT v, round(v, 1) FROM nvals;
v | round
-------+-------
-0.25 | -0.3
-0.15 | -0.2
-0.05 | -0.1
0.05 | 0.1
0.15 | 0.2
0.25 | 0.3
(6 rows)
Interesting, because round(v dp)
uses half even:
test=# create table vals (v double precision);
CREATE TABLE
test=# insert into vals (v) VALUES (-2.5), (-1.5), (-0.5), (0.5), (1.5), (2.5);
INSERT 0 6
test=# select v, round(v) from vals;
v | round
------+-------
-2.5 | -2
-1.5 | -2
-0.5 | -0
0.5 | 0
1.5 | 2
2.5 | 2
(6 rows)
The latter behavior is almost certainly platform-dependent, since it looks like it uses rint(3) under the hood.
You could always implement a different rounding scheme if necessary. See Tometzky's answer for examples.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With