In Postgres, Confusion for the conversion of Numeric/Real/Double Precision to Character Varying / Text

Question

For the conversion of Numeric/Real/Double Precision to Character Varying / Text.

select (12.0/100)::Double Precision;# 0.12
select (12.0/100)::Double Precision::Text;# 0.119999999999999996
select 0.12::text ; # 0.12
select (12.0/100)::Numeric::Text ; #0.12000000000000000000

0.12::text is correct, but the result of (12.0/100)::Numeric::Text and (12.0/100)::Double Precision::Text are confusing.

Laurenz Albe · Accepted Answer · 2018-08-09 12:05:37Z

2

The values might confuse you, but they are correct.

It seems like you set the parameter extra_float_digits to 3 (or you are using JDBC, which does that for you).

double precision is a floating point type and hence imprecise. With the default 0 for extra_float_digits you won't notice that, because the value is truncated so that only significant digits are shown, but if you request full precision, you'll see the rounding error in all its glory.

The last command will show many zeroes because you didn't specify numeric(10,2) to limit the number of decimal digits, and division potentially produces a lot of those. If you try * instead of /, you'll see what I mean.

answered Aug 9, 2018 at 12:05

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

In Postgres, Confusion for the conversion of Numeric/Real/Double Precision to Character Varying / Text

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related