How to ROUND double precision type values?

user_12200086 0
Viewed 6465

I use pgAdmin 4.. and I am trying to figure out, how to round column rental_period, which was extracted from deducting 2 timestamp columns and now consists out of double precision data type. I want to round it on 2 decimal places. Here is the script that returns error:

SELECT customer_id, ROUND (AVG (extract (day from (return_date - rental_date))), 2) AS rental_period
FROM rental
GROUP BY customer_id;

The error says:

ERROR: syntax error at or near "AS"

LINE 1: ...xtract (day from (return_date - rental_date)), 2) AS rental_... ^ SQL state: 42601

Character: 84

And here is a script which works fine, but returns too many decimal places:

SELECT customer_id, AVG (extract (day from (return_date - rental_date))) AS rental_period
FROM rental
GROUP BY customer_id;

Thank you)

postgresql sql
3 Answers

I cannot reproduce the error you are showing, there must be a syntax problem with your actual code.

However, this fails (with a different error):

SELECT round(
          EXTRACT(day FROM (DATE '2019-06-30')),
          2
       );
ERROR:  function round(double precision, integer) does not exist
LINE 1: SELECT round(EXTRACT(day FROM (DATE '2019-06-30')), 2);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

The reason is that there is no two-argument round function with double precision as first argument:

\df round
                         List of functions
   Schema   | Name  | Result data type | Argument data types | Type 
------------+-------+------------------+---------------------+------
 pg_catalog | round | double precision | double precision    | func
 pg_catalog | round | numeric          | numeric             | func
 pg_catalog | round | numeric          | numeric, integer    | func
(3 rows)

You will have to add an explicit type cast to numeric:

SELECT round(
          CAST (
             EXTRACT(day FROM (DATE '2019-06-30'))
             AS numeric
          ),
          2
       );
 round 
-------
 30.00
(1 row)
user_64643080

try casting to decimal type

AVG cast((extract (day from (return_date - rental_date))) as decimal(10,2)) AS rental_period
user_96879100

Subtracting two dates returns the difference in days. That's already a numeric, so there's no need to use extract:

SELECT  customer_id
,       ROUND(AVG(return_date - rental_date), 2) AS rental_period
FROM    rentals
GROUP BY
        customer_id

See it working at rextester.

user_505520
Related