PostgreSQL Rounding issue in output-CodePudding

My numeric query output is not functioning properly with infinite decimals. The column is of the type 'numeric'. The output is fine except when the decimal can be infinite as shown below like: '1.3333333333333333'

Query's I have tried

CAST(sum(column) as decimal(18,2)) / 60

ROUND(sum(column),2) / 60

I've been working with MySQL for a while and these query's function properly there. I must be missing something Postgres specific.

Output

1
0
0
3
57.5
0.5
1.3333333333333333

CodePudding user response：

you can use easy cast:

postgres=# select 4/3.0;
┌────────────────────┐
│      ?column?      │
╞════════════════════╡
│ 1.3333333333333333 │
└────────────────────┘
(1 row)

postgres=# select (4/3.0)::numeric(30,2);
┌─────────┐
│ numeric │
╞═════════╡
│    1.33 │
└─────────┘
(1 row)

CodePudding user response：

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