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

Question

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?

Faxun

Your error message doesn't match the code in your question. – Conyers

The syntax error aside, this closely related question on dba.SE sheds some light on rounding double precision numbers in PostgreSQL. – Hereford

@muistooshort, Thanks for pointing that out. It should say 'round' where it says 'avg'. Edited. – Faxun

for searching results sake, I also get this hint as output from the prompt: HINT: No function matches the given name and argument types. You might need to add explicit type casts. – Pax

Answer 1

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.

Garland

Hmm. When I try out ROUND(CAST(FLOAT8 '3.1415927' AS NUMERIC),2);, I get '0.314E1'. And I have my code written as ROUND(AVG(val),2) yet still get the error I described in my question. – Faxun

I just ran ROUND(CAST(FLOAT8 '3.1415927' AS NUMERIC),2); on PgAdmin and Ruby. With PgAdmin, I get 3.14, but with Ruby (using he Sequel gem) I get '0.314E1'. I wonder why this is... – Faxun

"For some odd reason the version of round that takes a precision is only available for numeric." Floating-point numbers are "useful approximations". If you ask code to round a floating-point number to two decimal places, returning another floating-point number, there's no guarantee that the closest approximation to the "right" answer will have only two digits to the right of the decimal. Numerics are effectively scaled integers; they don't have that problem. – Hypersthene

@Catcall Good point - a double version of round would need to return numeric or (ugh) text, so it might as well take a numeric argument. – Garland

@Faxun Ruby, or Ruby's pg gem, must do weird things with numeric values. Are you formatting it for display to the user? If so, see updated answer. – Garland

@Faxun If you're still seeing an error after casting to numeric, please show your real code. – Garland

@CraigRinger It does seem that the gem does weird things. However, your TO_CHAR code worked. The result has blank spaces at the beginning of it, but I can strip those off with Ruby. – Faxun

@Faxun As per the documentation, if you don't want the padding for the sign use the FM version, eg SELECT to_char(float8 '3.1415927', 'FM999999999.00'). Answer amended. – Garland

For those trying to find the comment by @Catcall : now it is Mike Sherrill 'Cat Recall' – Revivify

ROUND(value::numeric,2) -- when value is float8 – Leucopenia

When I use round((price / (weight / 1000)::numeric), 2) as price_per_kg, I get this weird "scientific notation" output like 0.8e-1 instead of 0.08. How can I fix it? – Miramontes

@JoreJoh use the format function or other number-to-text formatting functions to display it in your preferred form – Garland

Answer 2

        ((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.
...But, as definitive solution, you can overload the ROUND function.

Overloading as casting strategy

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

Now your instruction will works fine, try this complete comparison:

SELECT trunc(n,3), round(n,3) n_round, round(f,3) f_round,
       pg_typeof(n) n_type, pg_typeof(f) f_type, pg_typeof(round(f,3)) f_round_type
FROM (SELECT 2.0/3.0, 2/3::float) t(n,f);

trunc	n_round	f_round	n_type	f_type	f_round_type
0.666	0.667	0.667	numeric	double precision	numeric

The ROUND(float,int) function is f_round, it returns a (decimal) NUMERIC datatype, that is fine for some applications: problem solved!

In another applications we need a float also as result. An alternative is to use round(f,3)::float or to create a round_tofloat() function. Other alternative, overloading ROUND function again, and using 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
  accuracy float   -- accuracy, the "counting unit"
) RETURNS float AS $f$
   SELECT ROUND($1/accuracy)*accuracy
$f$ language SQL IMMUTABLE;

Try

  SELECT round(21.04, 0.05);     -- 21.05 float!
  SELECT round(21.04, 5::float); -- 20
  SELECT round(1/3., 0.0001);    -- 0.3333
  SELECT round(2.8+1/3., 0.5);   -- 3.15
  SELECT round(pi(), 0.0001);    -- 3.1416

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

Rounding and formating

The to_char function apply internally the round procedure, so, when your aim is only to show a final result in the terminal, you can use the FM modifier as a prefix to a numeric format pattern:

SELECT round(x::numeric,2), trunc(x::numeric,2), to_char(x, 'FM99.99')
FROM (SELECT 2.0/3) t(x);

round	trunc	to_char
0.67	0.66	.67

---

NOTES

Cause of the problem

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

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.

---

Round to the nth bit or other numeric representation

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

Tolyl

See also the new duplicated "Is there a function to round a floating point number directly?" – Tolyl

Answer 3

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

Issuant

I find this to be the much more concise and strait forward move on with my day answer to this question. :bow: – Bryozoan

Same here! Very short and useful solution. – Orville

This is not an algebric solution, it is valid only for print report. – Tolyl

Answer 4

you can use the function below

 SELECT TRUNC(14.568,2);

the result will show :

14.56

you can also cast your variable to the desire type :

 SELECT TRUNC(YOUR_VAR::numeric,2)

Overspread

Same problem, need cast to numeric and returns numeric – Tolyl

this isn't rounding, to be exact. the output for your suggestion is 14.56, whereas if you round that number, it would be 14.57 – Runt

Answer 5

SELECT ROUND(SUM(amount)::numeric, 2) AS total_amount
FROM transactions

Gives: 200234.08

Gavrilla

Answer 6

Try casting your column to a numeric like:

SELECT ROUND(cast(some_column as numeric),2) FROM table

Plumcot

working in pg10 without casting with numeric values. – Usa

@SergioBelevskij ERROR: function round(double precision, integer) does not exist, the cast as numeric is needed. – Capua

Try to cast value into numeric: round (cast(doublecolumn as numeric),2) if your pg version under 10. – Usa

Answer 7

According to Bryan's response you can do this to limit decimals in a query. I convert from km/h to m/s and display it in dygraphs but when I did it in dygraphs it looked weird. Looks fine when doing the calculation in the query instead. This is on postgresql 9.5.1.

select date,(wind_speed/3.6)::numeric(7,1) from readings;

Microdot

Answer 8

Error:function round(double precision, integer) does not exist

Solution: You need to addtype cast then it will work

Ex: round(extract(second from job_end_time_t)::integer,0)

Apocynaceous