How to round an average to 2 decimal places in PostgreSQL?

PostgreSQL does not define round(double precision, integer). For reasons @Mike Sherrill 'Cat Recall' explains in the comments, the version of round that takes a precision is only available for numeric.

regress=> SELECT round( float8 '3.1415927', 2 );ERROR:  function round(double precision, integer) does not existregress=> \df *round*                           List of functions   Schema   |  Name  | Result data type | Argument data types |  Type  ------------+--------+------------------+---------------------+-------- pg_catalog | dround | double precision | double precision    | normal pg_catalog | round  | double precision | double precision    | normal pg_catalog | round  | numeric          | numeric             | normal pg_catalog | round  | numeric          | numeric, integer    | normal(4 rows)regress=> SELECT round( CAST(float8 '3.1415927' as numeric), 2); round -------  3.14(1 row)

(In the above, note that float8 is just a shorthand alias for double precision. You can see that PostgreSQL is expanding it in the output).

You must cast the value to be rounded to numeric to use the two-argument form of round. Just append ::numeric for the shorthand cast, like round(val::numeric,2).

If you're formatting for display to the user, don't use round. Use to_char (see: data type formatting functions in the manual), which lets you specify a format and gives you a text result that isn't affected by whatever weirdness your client language might do with numeric values. For example:

regress=> SELECT to_char(float8 '3.1415927', 'FM999999999.00');    to_char    --------------- 3.14(1 row)

to_char will round numbers for you as part of formatting. The FM prefix tells to_char that you don't want any padding with leading spaces.

Try also the old syntax for casting,

SELECT ROUND(AVG(some_column)::numeric,2)    FROM table;

works with any version of PostgreSQL.

There are a lack of overloads in some PostgreSQL functions, why (???): I think "it is a lack" (!), but @CraigRinger, @Catcall and the PostgreSQL team agree about "pg's historic rationale".

PS: another point about rounding is accuracy, check @IanKenney's answer.

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 NUMERIC type... To preserve the first commom-usage overload, we can return a FLOAT type 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... complete!                ELSE 'NaN'::float  -- like an 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: checking \df round after overloadings, will show something like,

 Schema     |  Name | Result data type | Argument data types ------------+-------+------------------+---------------------------- myschema   | round | double precision | double precision, text, int myschema   | round | numeric          | double precision, int pg_catalog | round | double precision | double precision             pg_catalog | round | numeric          | numeric    pg_catalog | round | numeric          | numeric, int          

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

Try with this:

SELECT to_char (2/3::float, 'FM999999990.00');-- RESULT: 0.67

Or simply:

SELECT round (2/3::DECIMAL, 2)::TEXT-- RESULT: 0.67