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.