Question 89477 in PostgreSQL Question posted in
The official documentation can be found here.

I want to typecast in SQL to numeric(5,1) in order to have values round to one decimal point – however, this fails to work – Postgresql

I’m currently calculating ‘fizziness’ which is equal to volume * fizzyfactor / 100.

However, the values become very long floats. I want to round them to one decimal point.

When typecasting with numeric, it still has the same output. Why? How can I fix this ?

Here is my query:

create or replace view fizz(softdrink, store, fizziness)
as
select name, store, (volume * fizzyfactor/100)::decimal(5,1) as fizziness

And my output looks like so:
Column under 'fizziness' looks like this instead of having one decimal point

Tags:

2

Answers


  1. 0

    Is there any reason you cant use the SQL Round() function?
    if not you could use this query to round to exactly one decimal point

    create or replace view fizz(softdrink, store, fizziness)
as
select name, store, Round((volume * fizzyfactor/100),1) as fizziness

    you can read more about the Round() function here:

    https://www.postgresqltutorial.com/postgresql-math-functions/postgresql-round/

    Login or Signup to reply.
  2. 0

    When typecasting … it still has the same output. Why?

    Please recheck your data base

    See the example below working with ::decimal(5,1) as expected; same result obtained with round

    with dt as ( 
select * from (values 
(13, .7),
(12345, .1345)
) dt(volume, fizzyfactor)
)
select volume, fizzyfactor,
(volume * fizzyfactor/100) as fizziness,
(volume * fizzyfactor/100)::decimal(5,1) as fizziness_cast,
round((volume * fizzyfactor/100),1) as fizziness_round
from dt;

volume|fizzyfactor|fizziness             |fizziness_cast|fizziness_round|
------+-----------+----------------------+--------------+---------------+
    13|        0.7|0.09100000000000000000|           0.1|            0.1|
 12345|     0.1345|   16.6040250000000000|          16.6|           16.6|
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search