how to retain the decimal part in redshift when the decimal part is only having 0

Question

In my code we are using a multiplication as (180.00*60.00). Ideally since both the numbers are of decimal type I'm expecting my answer to be also in decimal. But in redshift I observed that this equation is resulting only to Integer. Basically when this multiplication happens I want the answer as 10800.0000 and not 10800. how to achieve this in Redshift. I tried putting the cast to whole expression and also even to individual expression but still I'm getting the answer only in Integer. any help here?

Answer 1

If I understand your question correctly you are asking why Redshift isn't inferring that the string "180.00*60.00" as being the multiplication of 2 values in decimal type, yes? If correct you just need to cast the input values for the multiplication. For example:

select 180.00::decimal(6,2) * 60.00::decimal(6,2) ;

It also looks like you want the result to have 4 significant digits so you may want to cast to decimal(8,4).

If I've missed the point of your question please clarify.