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

I am using PostgreSQL via the Ruby gem 'sequel'.

I'm trying to round to two decimal places.

Here's my code:

SELECT ROUND(AVG(some_column),2)    
FROM table

I get the following error:

PG::Error: ERROR:  function round(double precision, integer) does 
not exist (Sequel::DatabaseError)

I get no error when I run the following code:

SELECT ROUND(AVG(some_column))
FROM table

Does anyone know what I am doing wrong?

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 exist

regress=> \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.

((this is a Wiki! please edit to enhance!))

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".

Overloading as casting strategy

You can overload the ROUND function with,

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

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

  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, is to return a float when the accuracy is defined (see IanKenney's answer),

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.

Note about performance and reuse

The build-in functions, such as ROUND of the pg_catalog, can be overloaded with no performance loss, when compared to direct cast encoding. Two precautions must be taken when implementing user-defined cast functions for high performance:

The IMMUTABLE clause is very important for code snippets like this, because, as said in the Guide: "allows the optimizer to pre-evaluate the function when a query calls it with constant arguments"
PLpgSQL is the preferred language, except for "pure SQL". For JIT optimizations (and sometimes for parallelism) language SQL can obtain better optimizations. Is something like copy/paste small piece of code instead of use a function call.

Conclusion: the above ROUND(float,int) function, after optimizations, is so fast than @CraigRinger's answer; it will compile to (exactly) the same internal representation. So, although it is not standard for PostgreSQL, it can be standard for your projects, by a centralized and reusable "library of snippets", like pg_pubLib.

Generic round(float) returns float

Some people argue that it doesn't make sense for PostgreSQL to round a number of float datatype, because float is a binary representation, it requires rounding the number of bits or its hexadecimal representation.

Well, let's solve the problem, adding an exotic suggestion... The aim here is to return a float type in another overloaded function,
ROUND(float, text, int) RETURNS float
The text is to offer a choice between

'dec' for "decimal representation",
'bin' for "binary" representation and
'hex' for hexadecimal representation.

So, in different representations we have a different interpretation about the number of digits to be rounded. Rounding a number x with an approximate shorter value, with less "fractionary digits" (tham its original d digits), will be shorter when d is couting binary digits instead decimal or hexadecimal.

It is not easy without C++, using "pure SQL", but this code snippets will illustrate and can be used as workaround:

-- Looking for a round_bin() function! this is only a workaround:
CREATE FUNCTION trunc_bin(x bigint, t int) RETURNS bigint AS $f$
    SELECT ((x::bit(64) >> t) << t)::bigint;
$f$ language SQL IMMUTABLE;
 
CREATE FUNCTION ROUND(
   x float, 
   xtype text,  -- 'bin', 'dec' or 'hex'
   xdigits int DEFAULT 0
) 
RETURNS FLOAT AS $f$
    SELECT CASE
       WHEN xtype NOT IN ('dec','bin','hex') THEN 'NaN'::float
       WHEN xdigits=0 THEN ROUND(x)
       WHEN xtype='dec' THEN ROUND(x::numeric,xdigits)
       ELSE (s1 ||'.'|| s2)::float
      END
    FROM (
      SELECT s1,
             lpad( 
               trunc_bin( s2::bigint, CASE WHEN xd<bin_bits THEN bin_bits - xd ELSE 0 END )::text,
               l2,
               '0'
             ) AS s2
      FROM (
        SELECT *, 
             (floor( log(2,s2::numeric) ) +1)::int AS bin_bits, -- most significant bit position
             CASE WHEN xtype='hex' THEN xdigits*4 ELSE xdigits END AS xd
        FROM (
          SELECT s[1] AS s1, s[2] AS s2, length(s[2]) AS l2
          FROM (SELECT regexp_split_to_array(x::text,'\.')) t1a(s)
        ) t1b
      ) t1c
    ) t2
$f$ 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');   -- ERROR, need to cast string 
 SELECT round(2.8+1/3.,'dec'::text); -- 3 float
 SELECT round(2.8+1/3.,'dec',0); -- 3 float

 SELECT round(2.8+1/3.,'hex',0); -- 3 float (no change)
 SELECT round(2.8+1/3.,'hex',1); -- 3.1266
 SELECT round(2.8+1/3.,'hex',3); -- 3.13331578486784

 SELECT round(2.8+1/3.,'bin',1);  -- 3.1125899906842625
 SELECT round(2.8+1/3.,'bin',6);  -- 3.1301821767286784
 SELECT round(2.8+1/3.,'bin',12); -- 3.13331578486784

And \df round have also:

 Schema     |  Name | Result  | Argument  
------------+-------+---------+---------------
 myschema   | round | float   | x float, xtype text, xdigits int DEFAULT 0

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

Overloading as casting strategy

Note about performance and reuse

Generic round(float) returns float

Related

Recent Posts