postgresql round half down function

Question

the round(numeric,integer) function in PostgreSQL only rounds up:

round(cast (41.0255 as numeric),3) ==> 41.026

Since we need a round function which returns 41.025 and (quite surprisingly) there isn't such a function in PostgreSQL (we're using 9.1.5), we wrote a "wrapper" function which in the very first version is quite naive and rough...but we didn't find anything better due the lack of native support for this kind of problems in plpgsql.

The code is showed below. The problem is that it's too slow for our purposes. Could you suggest a better way to deal with this task?

Here's the code:

    CREATE OR REPLACE FUNCTION round_half_down(numeric,integer) RETURNS numeric 
    AS $$
    DECLARE
      arg ALIAS FOR $1;
      rnd ALIAS FOR $2;
      tmp1 numeric;
      res numeric;
    BEGIN
      tmp1:=arg;
      IF cast(tmp1 as varchar) ~ '5$'  THEN res:=trunc(arg,rnd);
      ELSE res:=round(arg,rnd);
      END IF;

      RETURN res;
    END;
    $$ LANGUAGE plpgsql;

I need to cast the numeric value and use regexp...that's what (I suppose) kills performances.

Just so you know: we need this because we have to compare numbers that were stored in two different columns (on two different tables) but with different numeric data type: one is double and one is real. The problem is that when inserting into a real data type column, PostgreSQL performs ROUND HALF DOWN while it doesn't provide such option via its mathematical functions!

EDIT:
The function is actually bugged. Was a first quick rewriting as an attempt to improve performances of a working function but very slow.

Behavior must match the following:
IF the decimal digit being put off from rounding is <=5 => trunc
ELSE round up.

Some examples:

select round_half_down(cast (41.002555 as numeric),3) -- 41.002 
select round_half_down(cast (41.002555 as numeric),4) -- 41.0025 
select round_half_down(cast (41.002555 as numeric),5) -- 41.00255 

while the round function in PostgreSQL gives:

select round(cast (41.002555 as numeric),3) -- 41.003

Chorea

Would you want -41.0255 rounded to -41.025 or -41.026? Also: Would you want 41.02551 rounded to 41.025 or 41.026? – Moller

Hi @erwin-brandstetter, The function is actually bugged. Was a first rewriting to improve performances of a working function but very slow. Behavior must match the following: IF the decimal digit being put off from rounding is <=5 => trunc ELSE round up. Some examples: select round_half_down(cast (41.002555 as numeric),3) -- 41.002 select round_half_down(cast (41.002555 as numeric),4) -- 41.0025 select round_half_down(cast (41.002555 as numeric),5) -- 41.00255 while the round function in postgresql: select round(cast (41.002555 as numeric),3) -- 41.003 – Chorea

I updated my answer to match your updates. BTW, this kind of information should go into your question, not into a comment where it is hard to read. – Moller

Answer 1

We have to compare numbers that were stored in two different columns (on two different tables) but with different numeric data type: one is double and one is real.

This should be extremely fast and simple:

SELECT dp_col, real_col
FROM   tbl
WHERE  dp_col::real = real_col

Basically, just cast the double precision number to real for comparison.

---

If that doesn't work for you, this SQL function should do a proper job and work faster:

CREATE OR REPLACE FUNCTION round_half_down1(numeric, int)
  RETURNS numeric LANGUAGE sql AS
$func$
SELECT CASE WHEN abs($1%0.1^$2) < .6 * 0.1^$2 THEN
         trunc($1, $2)
    ELSE round($1, $2) END;
$func$

Now fixed for negative numbers, with input from @sufleR in the comments.
You can also just use the contained CASE expression.

% .. modulo operator
^ .. exponentiation

---

Here is a quick test you can use for benchmarking:

SELECT n                                   -- Total runtime: 36.524 ms
      ,round_half_down1(n,3)               -- Total runtime: 70.493 ms
      ,round_down_to_decimal_places(n,3)   -- Total runtime: 74.690 ms
      ,round_half_down(n,3)                -- Total runtime: 82.191 ms
FROM  (SELECT random()::numeric AS n FROM generate_series(1,10000)) x
WHERE  round_down_to_decimal_places(n,3)
    <> round_half_down1(n,3)

It also demonstrates how @Parveen's function and your edited version miscalculate - they trunc() where they shouldn't.

Moller

@user500501: So did casting to real work for you or the function? – Moller

thanks a lot for your answer. The casting, strangely, doesn't work perfectly. I'm doing more tests to find out why, since it sounds the right (and simple) answer for my problem. Strangely, it doesn't match all the rows it should and it takes ages to find the non-matching ones to spot the problem. Basically,all values in the REAL column must match the "casted to REAL" values in the DOUBLE PRECISION column ('cause it's just what happened when values were inserted into REAL column) but it's not the case! 73761 rows out of 4267717 are not being matched. – Chorea

doesn't work for negative values. CASE WHEN $1%0.1^$2 should be CASE WHEN ABS($1%0.1^$2) – Bursitis

Answer 2

A method is very quick without creating a new FUNCTION which can round half down as below:

-- Round half up

round($n, 3)

-- Round half down

round($n-0.5, 3)

Dacha

This would just work for no digit after the period sign. For 1 digits use 0.05 and 2 digits, 0.005 and so on. – Wiper

Answer 3

Its Simple:

Try to use this function

CREATE OR REPLACE FUNCTION ROUND_HALF_DOWN(NUMERIC)
  RETURNS NUMERIC LANGUAGE SQL AS
$FUNC$
  SELECT CASE WHEN ($1%1) < 0.6 THEN FLOOR($1) ELSE CEIL($1) END;
$FUNC$

Smile

Answer 4

Here is a much simpler approach

CREATE OR REPLACE FUNCTION roundHalfDown(value NUMERIC, prec INTEGER)
RETURNS NUMERIC AS $$
BEGIN
  RETURN trunc(value * 10^prec + 0.5 - 0.000000001) / 10^prec;
END
$$ LANGUAGE 'plpgsql';

Bornstein

Answer 5

create or replace function round_down_to_decimal_places(numeric,integer)
returns numeric stable language sql as $$ 

select
case
when $1 >= 0 then
case when $1 - round($1, 3) < 0 then round($1, 3) - 0.001 else 
round($1, 3) end
else
case when $1 - round($1, 3) > 0 then round($1, 3) + 0.001 else 
round($1, 3) end
end

$$;

You can use generic by changing (0.001 for 3 decimal, 0.0001 for 4 decimal etc.,)

EDITED by the OP: @Parveel: I've modified your function in order to make it work in a general way.

create or replace function round_half_down(numeric,integer)
returns numeric stable language sql as $$ 

select
case
when $1 >= 0 then
    case 
        when ($1 - round($1, $2)) < 0 then cast((round($1, $2) - (1.0/(10^$2))) as numeric) 
    else round($1, $2) end
else
    case 
        when ($1 - round($1, $2)) > 0 then cast((round($1, $2) + (1.0/(10^$2))) as numeric)
    else round($1, $2) end
end

$$;

Tommy

Both your functions miscalculate. I added a demo to my answer. – Moller

@Erwin: actually the second one was a generalised version I proposed of the Parveen function :) – Chorea

@BangTheBank: It would be better to add your own answer if you have something substantially different (like here). SO does not discourage answering your own question. – Moller

@ErwinBrandstetter, thanks for pointing that out. I see your point but in this case I just took the original Parveen's idea and tried to generalize it. – Chorea

@BangTheBank: Still to substantial for an edit, should go into a separate answer. And if you do edit, you should make clear that the second half is from you (BangTheBank), not the original author (Parveen). A text like "I've modified ..." only becomes clear to readers after studying the edit history. Not how it should be. – Moller

@ErwinBrandstetter: ok, got it. This is the first time I'm actively using a stack site and didn't want to pollute with not really original answers, regardless the readability. – Chorea

@BangTheBank: I see your good intentions. Otherwise I wouldn't waste my time trying to educate. Welcome to SO! :) Now fix it one or the other way. – Moller

@ErwinBrandstetter: Thanks for your effort and the time you spend to do it:) I can't answer to my own questions since I don't have a sufficient amount of reputation points (I have to wait one day for that). Had to do it here. – Chorea

Answer 6

CREATE OR REPLACE FUNCTION public.round_half_down (numeric,integer)
RETURNS numeric AS
$body$
DECLARE
  arg ALIAS FOR $1;
  rnd ALIAS FOR $2;
  tmp1 numeric;
  res numeric;
  vra varchar;
  inta integer;
  ifa boolean;
 BEGIN
    tmp1:= arg;
    vra := substr(cast((arg - floor(arg)) as varchar),3);
    ifa := null;
    FOR i IN 1 .. length(vra) LOOP
        inta := CAST((substr(vra,i,1)) as integer);
        IF (i > rnd) THEN
            IF ((ifa is null) AND inta >= 6)THEN
                ifa := true;
            ELSE
                ifa := false;
            END IF;
        END IF;
    END LOOP;

    IF ifa THEN 
        res:=trunc(arg,rnd);
    ELSE 
        res:=round(arg,rnd);
    END IF;
    RETURN res;
END;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

Try this Hope can help u

Gauntlet