PostgreSQL - rounding floating point numbers

Question

I have a newbie question about floating point numbers in PostgreSQL 9.2.

Is there a function to round a floating point number directly, i.e. without having to convert the number to a numeric type first?

Also, I would like to know whether there is a function to round by an arbitrary unit of measure, such as to nearest 0.05?

When casting the number into a decimal form first, the following query works perfectly:

SELECT round(1/3.::numeric,4);

 round  
--------
 0.3333
(1 row)
Time: 0.917 ms

However, what really I'd like to achieve is something like the following:

SELECT round(1/3.::float,4);

which currently gives me the following error:

ERROR:  function round(double precision, integer) does not exist at character 8
Time: 0.949 ms

Thanks

Answer 1

You can accomplish this by doing something along the lines of

select round( (21.04 /0.05 ),0)*0.05

where 21.04 is the number to round and 0.05 is the accuracy.

Answer 2

Your workaround solution works with any version of PostgreSQL,

SELECT round(1/3.::numeric,4);

But the answer for "Is there a function to round a floating point number directly?", is no.

The cast problem

You are reporting a well-known "bug", there is a lack of overloads in some PostgreSQL functions... Why (???): I think "it is a lack" (!), but @CraigRinger, @Catcall (see comments at Craig's anser) and the PostgreSQL team agree about "PostgreSQL's historic rationale".

The solution is to develop a centralized and reusable "library of snippets", like pg_pubLib. It implements the strategy described below.

Overloading as casting strategy

You can overload the build-in ROUND function with,

 CREATE FUNCTION ROUND(float,int) RETURNS NUMERIC AS $f$
    SELECT ROUND($1::numeric,$2);
 $f$ language SQL IMMUTABLE;

Now your dream will be reality, try

  SELECT round(1/3.,4); -- 0.3333 numeric

It returns a (decimal) NUMERIC datatype, that is fine for some applications... An alternative is to use round(1/3.,4)::float or to create a round_tofloat() function.

Other alternative, to preserve input datatype and use all range of accuracy-precision of a floating point number (see IanKenney's answer), is to return a float when the accuracy is defined,

CREATE or replace FUNCTION ROUND(
  input    float,    -- the input number
  accuracy float     -- accuracy
) RETURNS float AS $f$
   SELECT ROUND($1/accuracy)*accuracy
$f$ language SQL IMMUTABLE;
COMMENT ON FUNCTION ROUND(float,float) IS 'ROUND by accuracy.';

Try

 SELECT round(21.04, 0.05);     -- 21.05 
 SELECT round(21.04, 5::float); -- 20 
 SELECT round(pi(), 0.0001);    -- 3.1416
 SELECT round(1/3., 0.0001);    -- 0.33330000000000004 (ops!)

To avoid floating-pont truncation (internal information loss), you can "clean" the result, for example truncating on 9 digits:

CREATE or replace FUNCTION ROUND9(
  input    float,    -- the input number
  accuracy float     -- accuracy
) RETURNS float AS $f$
   SELECT (ROUND($1/accuracy)*accuracy)::numeric(99,9)::float
$f$ language SQL IMMUTABLE;

Try

  SELECT round9(1/3., 0.00001);  -- 0.33333 float, solved!
  SELECT round9(1/3., 0.005);    -- 0.335 float, ok!

PS: the command \df round, on psql after overloadings, will show something like this table

 Schema     |  Name | Result  | Argument  
------------+-------+---------+------------------
 myschema   | round | numeric | float, int
 myschema   | round | float   | float, float
 pg_catalog | round | float   | float            
 pg_catalog | round | numeric | numeric   
 pg_catalog | round | numeric | numeric, int          

where float is synonymous of double precision and myschema is public when you not use a schema. The pg_catalog functions are the default ones, see at Guide the build-in math functions.

---

More details

See a complete Wiki answer here.