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

Try

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

works with any version of PostgreSQL.

There are a lack of overloads in some PostgreSQL functions, why (???): I think "it is a lack", and below show my workaround, but see this discussion for more explanations.

---

Overloading as casting strategy

You can overload the ROUND function with,

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

Now your instruction will works fine, try (after function creation)

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

but it returns a type NUMERIC... To preserve the first commom-usage overload, we can return a float when a text parameter is offered,

 CREATE FUNCTION ROUND(float, text, int DEFAULT 0) 
 RETURNS FLOAT AS $$
    SELECT CASE WHEN $2='dec'
                THEN ROUND($1::numeric,$3)::float
                -- ... WHEN $2='hex' THEN ... WHEN $2='bin' THEN... 
                ELSE 'NaN'::float  -- is like a error message 
            END;
 $$ language SQL IMMUTABLE;

Try

 SELECT round(1/3.,'dec',4);   -- 0.3333 float!
 SELECT round(2.8+1/3.,'dec',1); -- 3.1 float!
 SELECT round(2.8+1/3.,'dec'::text); -- need to cast string? pg bug 

---

PS: You can check the overloading by \df,

 \df round
  Schema    | Name  | Datatype of result        |    Datatype of parameters                       
 -----------+-------+---------------------------+--------------------------------
 myschema   | round | numeric                   | double precision, integer                                     
 myschema   | round | double precision          | double precision, text, integer
 pg_catalog | round | double precision          | double precision                                              
 pg_catalog | round | numeric                   | numeric                                                       
 pg_catalog | round | numeric                   | numeric, integer                                              

The pg_catalog functions are the default ones, see manual of build-in math functions.

Answer 2

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.