Getting float values out of PostgreSQL

Question

I am having trouble retrieving float/real values out of PostgreSQL.

For example, I would like to store: 123456789123456, the retrieve that exact same number with a select statement.

table tbl (num real) insert into tbl(num) values('123456789123456');

As it is now, if I "select num from tbl" the result is "1.23457e+14" If I run "select CAST(num AS numeric) as num from tbl" the result is 123457000000000 If I run "select CAST(num AS float) as num from tbl" the result is 123456788103168 (where did this number come from)

How on earth can I select the value and get "123456789123456" as the result?

Thanks so much in advance

You're only going to get about 6.5 digits of accuracy with a real or single-precision floating point. — Thanatos
– Thanatos, Commented Jun 8, 2010 at 0:25

Milen A. Radev · Accepted Answer · 2010-06-08 00:29:57Z

4

You declared the table with the column having a type of "real", which is a fairly low-precision floating-point number.

You probably want to use the type "double precision" (aka "float" or "float8") for a reasonable degree of floating-point accuracy. If you know the magnitude and precision of numbers you need to store, you may be better off declaring the column type as numeric(PREC,SCALE) instead - PREC being the total number of digits to keep, and SCALE the number of digits that will be to the right of the decimal point.

edited Jun 8, 2010 at 0:29

Milen A. Radev

63.2k22 gold badges111 silver badges112 bronze badges

answered Jun 8, 2010 at 0:24

araqnid

135k25 gold badges164 silver badges139 bronze badges

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

Comments

Kknd · Accepted Answer · 2010-06-08 00:27:15Z

1

The real type has only 6 decimal digits of precision, so it can't store your number exactly. You may need to use "double precision" or "numeric/decimal" type.

Source: http://www.postgresql.org/docs/8.4/static/datatype-numeric.html .

answered Jun 8, 2010 at 0:27

Kknd

3,2671 gold badge28 silver badges30 bronze badges

Collectives™ on Stack Overflow

Getting float values out of PostgreSQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related