PostgreSQL - rounding floating point numbers

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


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 FUNCTION round(
  input float,    -- the input number
  dec_digits int, -- decimal digits to reduce precision
  accuracy float  -- compatible accuracy, a "counting unit"
) RETURNS float AS $f$
   SELECT round($1/accuracy,dec_digits)*accuracy
$f$ language SQL IMMUTABLE;

Try

  SELECT round(21.04, 0, 0.05);    -- 21.05 float!
  SELECT round(1/3., 4, 0.0001);   -- 0.3333
  SELECT round(2.8+1/3., 1, 0.5);  -- 3.15

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, int, 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.


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.