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)) / 60ROUND(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
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)
ROUND(sum(column) / 60::numeric(30,2),2) Thanks for getting me into the right direction!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).
