Geek Answers Handbook

PostgreSQL round (v numeric, s int)

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

  • Round half up
  • Round and a half down
  • Within a radius of one and a half from zero
  • Round half to zero
  • Round half even
  • Round half - odd

I am looking for a link to the documentation.

+6
source share
2 answers

Sorry, I don’t see any hint of this in the documentation, but a look at the code indicates that it uses a round half from zero; carry always added to digits , thereby increasing the absolute value of a variable regardless of its sign . 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 parity:

 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 almost certainly depends on the platform, as it looks like it uses rint (3) under the hood .

If necessary, you can always implement a different rounding scheme. See Tometzky's examples.

+5
source

It is not documented, so it can change.

Here is 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 500,000 calls per second, 6 times slower than the standard round(numeric,integer) . They also work for zero and for negative accuracy.

+3
source

More articles:


All Articles