|The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language Reference
by The PostgreSQL Global Development Group
Paperback (6"x9"), 454 pages
RRP £14.95 ($19.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
6.1.3 Floating-Point Types
The data types
precision are inexact, variable-precision numeric types.
In practice, these types are usually implementations of
IEEE Standard 754 for Binary Floating-Point
Arithmetic (single and double precision, respectively), to the
extent that the underlying processor, operating system, and
compiler support it.
Inexact means that some values cannot be converted exactly to the internal format and are stored as approximations, so that storing and retrieving a value might show slight discrepancies. Managing these errors and how they propagate through calculations is the subject of an entire branch of mathematics and computer science and will not be discussed here, except for the following points:
If you require exact storage and calculations (such as for
monetary amounts), use the
- If you want to do complicated calculations with these types for anything important, especially if you rely on certain behavior in boundary cases (infinity, underflow), you should evaluate the implementation carefully.
- Comparing two floating-point values for equality might not always work as expected.
On most platforms, the
real type has a range of at least
1E-37 to 1E+37 with a precision of at least 6 decimal digits. The
double precision type typically has a range of around
1E-307 to 1E+308 with a precision of at least 15 digits. Values that
are too large or too small will cause an error. Rounding might
take place if the precision of an input number is too high.
Numbers too close to zero that are not representable as distinct
from zero will cause an underflow error.
In addition to ordinary numeric values, the floating-point types have several special values:
These represent the IEEE 754 special values
“infinity”, “negative infinity”, and
“not-a-number”, respectively. (On a machine whose
floating-point arithmetic does not follow IEEE 754, these values
will probably not work as expected.) When writing these values
as constants in an SQL command, you must put quotes around them,
UPDATE table SET x = 'Infinity'. On input,
these strings are recognized in a case-insensitive manner.
Note: IEEE754 specifies that
NaNshould not compare equal to any other floating-point value (including
NaN). In order to allow floating-point values to be sorted and used in tree-based indexes, PostgreSQL treats
NaNvalues as equal, and greater than all non-
PostgreSQL also supports the SQL-standard
float(p) for specifying
inexact numeric types. Here, p specifies
the minimum acceptable precision in binary digits.
float(24) as selecting the
real type, while
double precision. Values of p
outside the allowed range draw an error.
float with no precision specified is taken to mean
Note: Prior to PostgreSQL 7.4, the precision in
float(p)was taken to mean so many decimal digits. This has been corrected to match the SQL standard, which specifies that the precision is measured in binary digits. The assumption that
double precisionhave exactly 24 and 53 bits in the mantissa respectively is correct for IEEE-standard floating point implementations. On non-IEEE platforms it might be off a little, but for simplicity the same ranges of p are used on all platforms.
|ISBN 9781906966041||The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language Reference||See the print edition|