Postgres Floating-point data types
Working with float values in PostgreSQL
In Postgres, floating point data types are used to represent numbers that might have a fractional part. These types are essential for situations where precision is key, such as scientific calculations, financial computations, and more.
Storage and syntax
Postgres supports two primary floating-point types:
REAL: Also known as "single precision,"
REALoccupies 4 bytes of storage. It offers a precision of at least 6 decimal digits.
DOUBLE PRECISION: Known as "double precision," this type uses 8 bytes of storage and provides a precision of at least 15 decimal digits.
Both types are approximate numeric types, meaning they may have rounding errors and are not recommended for storing exact decimal values, like monetary data.
For a weather data application,
REAL might be used for storing temperature readings, where extreme precision isn't critical, as in the following example:
For more complex scientific calculations involving extensive decimal data,
DOUBLE PRECISION would be more appropriate, as in this example:
Floating-point types support the standard arithmetic operations: addition, subtraction, multiplication, division, and modulus. However, operations like division might lead to potential rounding errors and precision loss.
This query yields
3.3333333333333333, which does not represent the quantity
10 / 3 exactly, but rather rounded to the nearest representable value. When performing a series of operations, these rounding errors can accumulate and lead to significant precision loss.
Special Floating-point values
Postgres floating-point types can represent special values like
'NaN' (not a number). These values can be useful in certain mathematical or scientific computations.
Consider a table named
calculations, which might store the results of various scientific computations, including temperature changes, pressure levels, and calculation errors that could potentially result in
Notice that you must use single quotes to wrap these values as shown above.
- Accuracy and rounding: Be aware of rounding errors. For applications requiring exact decimal representation (like financial calculations), consider using
- Performance: While
DOUBLE PRECISIONoffers more precision, it might not be as performant due to the larger storage size.
Last updated on