The fourth in a series of blogs covering common mistakes in Database and Application designs for financial calculations.

Database Driver, or Application Framework Created Error:

The database driver or application framework created errors are probably the hardest to find, as we are the consumer not the writer of the tool, with many assuming the casting is correct. However, we must review the documentation or the library’s code to know how the data type is mapped in the framework. Keep in mind, PostgreSQL numeric type does not always have a comparable data type in many frameworks.

Frameworks and languages casting numeric to less accurate type:

Java Script

• Numeric => 64 bit float
• Probably the weakest framework to use for accurate math calculations.
• In its defense, it was never designed to do things it is asked to do today. (cough NodeJS cough )

PHP

• Numeric => 64 bit float
• Independent Math libraries must be used to accurately calculate results. Creates problems sending data back to PostgreSQL, as conversion to string or float must be used in the database driver.

Ruby

• Numeric => String
• Numeric => BigDecimal
• Ruby has a full featured Math library to accurately calculate results. Same problem that PHP has sending data back to PostgreSQL.

GO

• Numeric => unknown
• The drivers have no clear documentation on how numeric is being cast. Most likely it is being cast to a 64 bit float.
• There are libraries available to accurately represent PostgreSQL numeric types and do math operations on them, but they have the same problem PHP, and Ruby have.

Frameworks and languages with accurate types for numeric:

Python

• Numeric => Decimal
• Decimal is equivalent to Numeric and has appropriate Math library
• Pyscopg is a PostgreSQL community created driver

.Net

• Numeric => Decimal
• Decimal is equivalent to Numeric and has appropriate Math library
• Npgsql is a PostgreSQL community created driver

Java

• Numeric => BigDecimal
• BigDecimal is equivalent to Numeric and has appropriate Math library
• The database driver is supported by the PostgreSQL community

C/C++

• Direct access to the libpq library and PostgreSQL numeric type
• Direct access to Math libraries to be used with the numeric type

As these two lists show, the application framework can add errors to the calculations just through type casting, which later affects calculations.

The solution:

The application framework needs to be reviewed to make sure calculations are not using floating point types when it’s a critical calculation. Review the database driver layer to ensure incorrect type casting is not being performed. Use available Math libraries to do all critical calculations.

Personal Comment: It is kind of amazing to me with so many new frameworks and programming languages being developed over the last decade, how all the new toys lack accurate math libraries. It seems they all choose to go for speed over accuracy first, then go “Whoops! We need to be accurate too!” This is probably one of the reasons I see this mistake repeated so many times in application design, as no one realizes how these tiny errors stack up and bite.

Closing Thought:

Use the appropriate data types in the database and throughout the application stack. Python, C, C++ Java, .Net, all have libraries and functions that make doing these calculations easy.