PostgreSQL

Using Floating Data Types in PostgreSQL

Float data type falls under the numeric data type category. It supports floating point numbers., numeric, and real numbers. Floating data types can be confusing. But this guide will shed light on understanding the three main floating data types and how to represent them in PostgreSQL using different examples.

Using Floating-Point Numbers in PostgreSQL

Floating numbers contain decimal points. You could have positive floating-point numbers, such as 3677363, or negative values, such as -233.12. In either case, we must represent them on PostgreSQL as floating data types, which makes it possible to have numbers with infinite precision in the database.

Float data types in PostgreSQL store scientific numbers that can be calculated close to a value and range between 1E-307 to 1E+308.

PostgreSQL has three types of floating-point numbers.

1. Float (n)

It is used for floating numbers with an n precision and a maximum of 8 bytes. This floating-point number requires specifying the precision number when creating it. For instance, float4.

Let us create a table that takes a float as one of the data types.


With our table created, we can insert the values into the columns, including floating-point numbers.


Lastly, we can verify that our table has all the inserted column values.


That is how you work with float(n) floating-point numbers.

2. Real

It is a 4-byte floating-point data type. It takes values between 1E-37 TO 1E+37 and has an accuracy of 6 decimal digits.

Let us create a table to represent the real data type and insert values to its column.


Insert real floating-point numbers.


Our values are inserted as expected.

3. Numeric or Numeric (x, y)

It represents floating-point numbers with x digits and y numbers after the decimal points.

Numeric has a double precision range of 1E-307 to 1E+308. Still, it has an accuracy of 15 digits and the numeric (x, y) stands for the exact number and the double precision it has, making it numeric floating-point numbers store more precise values.

For instance, using numeric (3, 2) implies that the numeric value contains three digits and has two numbers after the decimal, such as 1.23, 0.10.etc.

We can represent the same in a table.


When inserting the values, ensure you insert values that match the numeric (x, y)


Suppose you insert a value with more digits after the decimal value than the specified y numbers; it will get truncated. For instance, insert a value with four digits after the decimal.


If we view the table, we note that every inserted numeric value has two numbers after the decimal, as specified when creating the table.


Under the weight column, we have all the numeric (x, y) values with two digits after the decimal.

Using Floating Data Types in One Table

We have understood the three floating-point numbers in PostgreSQL. Let us create a table that combines the three floating data types.


With the table created, let us insert values.


Use the SELECT keyword to verify the created table and its values.


We have managed to combine all the floating-point numbers in one table. That is how you use floating data types in PostgreSQL.

Conclusion

PostgreSQL has three floating-point data types: real, numeric (x, y), and float(n). The float(n) has a precision of n with eight maximum bytes, real has 4 bytes, and numeric (x, y) has x total digits with y digits after the decimal. This guide has detailed each of the floating data types with examples.

About the author

Denis Kariuki

Denis is a Computer Scientist with a passion for Networking and Cyber Security. I love the terminal, and using Linux is a hobby. I am passionate about sharing tips and ideas about Linux and computing.