Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PostgreSQL round(v numeric, s int)

Which method does Postgres round(v numeric, s int) use?

  1. Round half up
  2. Round half down
  3. Round half away from zero
  4. Round half towards zero
  5. Round half to even
  6. Round half to odd

I'm looking for documentation reference.

like image 354
mpapec Avatar asked Mar 24 '14 15:03

mpapec


People also ask

What is the difference between numeric and integer in PostgreSQL?

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.

Is int and integer same in PostgreSQL?

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.


2 Answers

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.

like image 152
Tometzky Avatar answered Sep 20 '22 07:09

Tometzky


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.

like image 22
Aryeh Leib Taurog Avatar answered Sep 22 '22 07:09

Aryeh Leib Taurog