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

Answer 1

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.

Answer 2

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 .