Postgres and Open Source Experts

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

Method of Rounding:

There are many methods of rounding

1. Half Round Up
2. Half Round Down
3. Round Towards Zero
4. Round Away from Zero
5. Round Half To Even
6. Round Half To Odd
7. Random Round

The built-in method of rounding in PostgreSQL is Half Round Up. Unfortunately, it is not the best approach, …

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 third in a series of blogs covering common mistakes in Database and Application designs for financial calculations.

Order of Operations and Storing Aggregate Results:

When working with float data types, order of operations will affect the ending value.

Consider 3:

Python3:

justin@Debian10:~$ python3

Python 3.7.3 (default, Jul 25 2020, 13:03:44) 

>>> (1234.567 * 3.333333) + (1.234567 * 3.333333)

4119.338,144,732,812

>>> (1234.567 + 1.234567 ) * 3.333333 …

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

Inconsistent precision scaling:

This is probably the most common mistake in database design that I observe. It is understood to use exact data types (such as numeric) and the precision must be fixed, but for whatever reason the decision is made that it’s OK for one table to use numeric(12,4),a second table …

A series on the use of data types to insure accurate financial calculations with your application.

Over my multi-decade career, I have often noticed the problematic use of real, floating, double, and fixed precision types to store and calculate financials. Most believe the application only needs two digits to the right of the decimal point for financial data. The use of only two digits assumes that many financial calculations do …