Considering Migration for NUMERIC and DECIMAL Data Types

From InterBase

NUMERIC and DECIMAL data types that have a precision greater than 9 are stored differently in dialect 1 and dialect 3 databases. As you migrate your databases to dialect 3, consider the following questions about columns defined with NUMERIC and DECIMAL data types:

  • Is the precision less than 10? There is no issue. You can migrate without taking any action and there will be no change in the database and no effect on clients.
  • For NUMERIC and DECIMAL columns with precision equal to or greater than 10, is DOUBLE PRECISION an appropriate way to store your data?
  • In many cases, the answer is “yes.” If you want to continue to store your data as DOUBLE PRECISION, change the audiotape of the column to DOUBLE PRECISION either before or after migrating your database to dialect 3. This doesn’t change any functionality in dialect 3, but it brings the declaration into line with the storage mode. In a dialect 3 database, newly-created columns of this type are stored as INT64, but migrated columns are still stored as DOUBLE PRECISION. Changing the declaration avoids confusion.
  • DOUBLE PRECISION might not be appropriate or desirable for financial applications and others that are sensitive to rounding errors. In this case, you need to take steps to migrate your column so that it is stored as INT64 in dialect 3. As you make this decision, remember that INT64 does not store the same range as DOUBLE PRECISION. Check whether you will lose information in this conversion and whether this is acceptable.