Why Is There a Precision Difference Between Floating-Point Data such as Double and Float Data in the Source and Destination Databases?

Symptom

When data is migrated through an offline data migration job, the precision of the floating-point data such as double and float data in the source database is different from that in the destination database.

Possible Causes

This issue is caused by the differences between database systems in the implementation and precision definition of the floating-point data. It does not result from data loss or errors caused by DataArts Migration.

The causes are as follows:

1. Floating-point data is not precise.

   Both the float (single-precision) and double (double-precision) data types are non-precise numeric types. Data of these types is stored in computers in compliance with the IEEE 754 standard, which uses binary to represent decimal numbers. Many decimal numbers (such as 0.1) cannot be precisely represented using binary. Therefore, there is a small precision error in the storage and calculation of these numbers.

   • REAL/FLOAT4: single-precision floating-point number, which supports 6-bit decimal digits
   • DOUBLE PRECISION/FLOAT8: double-precision floating-point number, which supports 15-bit decimal digits
2. The precision definition varies depending on the data source.

   Different database vendors implement the default precision, rounding rule, and number of displayed digits for floating-point numbers in different ways. The source and destination databases may use different mechanisms to process floating-point numbers.

Solution

In scenarios where high precision is required for data, such as high-precision scientific computing and financial calculation, you are advised to migrate data for which you can specify the precision, such as decimal data, to avoid precision differences.