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 @Catcall 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.
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
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)
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;