Back to articles list
Picking the right type for a column is important, but the best choice is not always obvious. A common beginner mistake is to use a floating point type to store financial values.
It is very important not to use floating point types (such as REAL, DOUBLE PRECISION, etc.) to store financial data. Calculations performed on floating point types can be inaccurate, which is unacceptable when working with business-related data. If you’re dealing with money, approximations are not sufficient!
Let’s see an example. If we run the following query:
SELECT CAST(0.1 AS FLOAT)+CAST(0.2 AS FLOAT)
The result will be:
0.30000000000000004
Floating points are represented as 
, where both 
and 
must be integers. That is the reason why it is sometimes impossible to express a number accurately using a floating point type. On the other hand, these data types can use less storage, although at a cost of reduced precision.
When working with financial data, it is best to use a decimal data type for its accuracy. All of the popular databases provide a decimal data type:
| Database | Decimal Data Type Name |
|---|---|
| PostgreSQL | DECIMAL |
| SQL Server | DECIMAL |
| MySQL | DECIMAL |
| Oracle | DECIMAL |
Let’s use a simple shop diagram as an example:
Our Product table has a column called price that uses the DECIMAL data type.
Where to Use Floating Point Data Types
Floating point types should be used for working with scientific data and measurements (such as length or weight). However, they are not suitable for financial data.
How to Change a Column’s Data Type in Vertabelo
To change a column’s data type in Vertabelo, first select the table. In the right pane, find the Columns section and click the gear icon next to the column type 
.
A new window containing the available data types will appear. The DECIMAL data type can be found in the Numeric section. You can configure length and precision for this data type.
