All About Floating-Point Types In PostgreSQL
This post is part of a series on the numeric data types found in PostgreSQL. Read on to learn all about defining arbitrary precision numbers in PostgreSQL. Write a comment if you have any questions or corrections!
|real||4 bytes||6 digits precision|
|double precision||8 bytes||15 digits precision|
Floating-point numbers are numbers that contain 'floating' decimal points. Examples: 5.5, 0.001, -2,345.3910.
The data types
double precision are used to store floating-point numbers. They are inexact, variable-precision numeric types. Inexact means that some values are stored as approximations, so storing a number and then retrieving that value might reveal slight differences.
Knowing this, if you require exact storage and calculations (for monetary information, for example), use
numeric instead. Additionally, if you want to do complicated calculations with these types, using a more precise type might be preferred. And, finally, when comparing two floating-point values for equality, it might not evaluate as you would expect.
If the above doesn't apply, then you may consider using a
double precision data type, as they are faster than
Let's see some examples to understand better how
double precision types work.
-- Table definition CREATE TABLE moar_num_tests ( numbah REAL, precise_numbah DOUBLE PRECISION ); /* Both numbers will be stored just as entered. The real data value is below 6 digits, and the double precision value is below 15 digits So when you retrieve them, they will still be 32.021 and 2340.203. */ INSERT INTO moar_num_tests VALUES(32.021, 2340.203); /* Both numbers will be stored just as entered. The real data value is at 6 digits, and the double precision value is at 15 digits So when you retrieve them, they will still be the same as when entered. */ INSERT INTO moar_num_tests VALUES(221.243, 302.103959025367); /* Both numbers will be rounded. So the real becomes 50.306 and the double precision becomes 7856864.89745456. Note that in the second case, it doesn't round to 7 as you might expect, instead it rounds to the nearest even number. */ INSERT INTO moar_num_tests VALUES(50.3059832, 7856864.8974545649); /* Inserting a whole number outside of the data type's range puts it in exponential notation (5.04033e+11, 7.85686423468975e+20) */ INSERT INTO moar_num_tests VALUES(504033059832, 785686423468974545649);
Aside from numeric values, floating-point types like
double precision also support the values
NaN. You must put quotes around them.
PostgreSQL also supports the SQL-standard notations of
FLOAT(p). P represents the minimum acceptable precision in binary digits. So:
More Numeric Types
Learn about the other numeric data types in one of the following posts:
What to see more of this kind of content? Let me know in the comments or reach out on Twitter!