How to round an average to 2 decimal places in PostgreSQL?
postgresql, ruby, sequel, sql
Solution
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.
Problem
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?