From: | raf <raf(at)raf(dot)org> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | any plans to support more rounding methods in sql? |
Date: | 2012-01-24 23:47:54 |
Message-ID: | 20120124234754.GA12425@raf.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Rob Sargent | 2012-01-25 00:10:32 | Re: Best way to create unique primary keys across schemas? |
Previous Message | Merlin Moncure | 2012-01-24 23:23:18 | Re: Best way to create unique primary keys across schemas? |