Spanner provides theNUMERIC type that can store decimal precision numbersexactly. The semantics of theNUMERIC type in Spanner variesbetween its two SQL dialects (GoogleSQL and PostgreSQL),especially around the limits onscale and precision:

• NUMERIC in thePostgreSQL dialect is anarbitrary decimalprecisionnumeric type (scale or precision can be any number within the supportedrange) and thus is an ideal choice for storing arbitrary precision numericdata.

• NUMERIC in GoogleSQL is afixed precisionnumeric type (precision=38 and scale=9) and cannot be used to storearbitrary precision numeric data. When you need to store arbitrary precisionnumbers in GoogleSQL dialect databases, we recommend that youstore them asstrings.

Precision of Spanner numeric types

Precision is the number of digits in a number. Scale is the number of digits tothe right of the decimal point in a number. For example, the number 123.456 hasa precision of 6 and a scale of 3. Spanner has three numerictypes:

• 64-bit signed integer type calledINT64 in the GoogleSQL dialectandINT8 in the PostgreSQL dialect.
• IEEE 64-bit (double) binary precision floating-point type calledFLOAT64in the GoogleSQL dialect andFLOAT8 in the PostgreSQLdialect.
• Decimal precisionNUMERIC type.

Let's look at each in terms of precision and scale.

INT64 /INT8 represents numericvalues that don't have a fractional component. This data type provides 18digits of precision, with a scale of zero.

FLOAT64 /FLOAT8 can only representapproximate decimal numeric values with fractional components and provides 15 to17 significant digits (count of digits in a number with all trailing zerosremoved) of decimal precision. We say that this type representsapproximatedecimal numeric values becauseIEEE 64-bit floatingpoint binary representation thatSpanner uses cannot precisely represent decimal (base-10)fractions (it can represent only base-2 fractions exactly). This loss ofprecision introduces rounding errors for some decimal fractions.

For example, when you store the decimal value 0.2 using theFLOAT64 /FLOAT8data type, the binary representation converts back to a decimal value of0.20000000000000001 (to 18 digits of precision). Similarly (1.4 * 165) convertsback to 230.999999999999971 and (0.1 + 0.2) converts back to0.30000000000000004. This is why 64-bit floats are described as only having15-17 significant digits of precision (only some numbers with more than 15decimal digits can be represented as 64-bit float without rounding). For moredetails on how floating point precision is calculated, seeDouble-precisionfloating-pointformat .

NeitherINT64 /INT8 norFLOAT64 /FLOAT8 has the ideal precision forfinancial, scientific, or engineering calculations, where a precision of 30digits or more is commonly required.

TheNUMERIC data type is suitable for those applications, since it is capableof representing exact decimal precision numeric values having precision of morethan 30 decimal digits.

The GoogleSQLNUMERIC datatype can represent numbers with a fixed decimal precision of 38 and fixed scaleof 9. The range of GoogleSQLNUMERIC is-99999999999999999999999999999.999999999 to99999999999999999999999999999.999999999.

The PostgreSQL dialectNUMERIC type can represent numbers with amaximum decimal precision of 147,455 and a maximum scale of 16,383.

If you need to store numbers that are larger than the precision and scaleoffered byNUMERIC, the following sections describe some recommendedsolutions.

Recommendation: store arbitrary precision numbers as strings

When you need to store an arbitrary precision number in a Spannerdatabase, and you need more precision thanNUMERIC provides, we recommend thatyou store the value as its decimal representation in aSTRING /VARCHARcolumn. For example, the number123.4 is stored as the string"123.4".

With this approach, your application must perform a lossless conversion betweenthe application-internal representation of the number and theSTRING /VARCHAR column value for database reads and writes.

Most arbitrary precision libraries have built-in methods to perform thislossless conversion. In Java, for example, you can use theBigDecimal.toPlainString()method and theBigDecimal(String)constructor.

Storing the number as a string has the advantage that the value is stored withexact precision (up to theSTRING /VARCHAR column length limit), and thevalue remains human-readable.

Perform exact aggregations and calculations

To performexact aggregations and calculations on string representations ofarbitrary precision numbers, your application must perform these calculations.You cannot use SQL aggregate functions.

For example, to perform the equivalent of a SQLSUM(value) over a range ofrows, the application must query the string values for the rows, then convertand sum them internally in the app.

Perform approximate aggregations, sorting, and calculations

You can use SQL queries to performapproximate aggregate calculations bycasting the values toFLOAT64 /FLOAT8.

SELECTSUM(CAST(valueASFLOAT64))FROMmy_table

SELECTSUM(value::FLOAT8)FROMmy_table

Similarly, you can sort by numeric value or limit values by range with casting:

SELECTvalueFROMmy_tableORDERBYCAST(valueASFLOAT64);SELECTvalueFROMmy_tableWHERECAST(valueASFLOAT64) >100.0;

SELECTvalueFROMmy_tableORDERBYvalue::FLOAT8;SELECTvalueFROMmy_tableWHEREvalue::FLOAT8 >100.0;

These calculations are approximate to the limits of theFLOAT64 /FLOAT8data type.

Alternatives

There are other ways to store arbitrary precision numbers inSpanner. If storing arbitrary precision numbers as strings doesnot work for your application, consider the following alternatives:

Store application-scaled integer values

To store arbitrary precision numbers, you can pre-scale the values beforewriting, so that numbers are always stored as integers, and re-scale the valuesafter reading. Your application stores a fixed scale factor, and the precisionis limited to the 18 digits provided by theINT64 /INT8 data type.

Take, for example, a number that needs to be be stored with an accuracy of 5decimal places. The application converts the value to an integer by multiplyingit by 100,000 (shifting the decimal point 5 places to the right), so the value12.54321 is stored as1254321.

In monetary terms, this approach is like storing dollar values as multiples ofmilli-cents, similar to storing time units as milliseconds.

The application determines the fixed scaling factor. If you change the scalingfactor, you must convert all of the previously scaled values in your database.

This approach stores values that are human-readable (assuming you know thescaling factor). Also, you can use SQL queries to perform calculations directlyon values stored in the database, as long as the result is scaled correctly anddoes not overflow.

Store the unscaled integer value and the scale in separate columns

You can also store arbitrary precision numbers in Spanner usingtwo elements:

• The unscaled integer value stored in a byte array.
• An integer that specifies the scaling factor.

First your application converts the arbitrary precision decimal into an unscaledinteger value. For example, the application converts12.54321 to1254321.The scale for this example is5.

Then the application converts the unscaled integer value into a byte array usinga standard portable binary representation (for example, big-endiantwo'scomplement).

The database then stores the byte array (BYTES /BYTEA) and integer scale(INT64 /INT8) in two separate columns, and converts them back on read.

In Java, you can useBigDecimalandBigIntegerto perform these calculations:

byte[] storedUnscaledBytes = bigDecimal.unscaledValue().toByteArray();int storedScale = bigDecimal.scale();

You can read back to a JavaBigDecimal using the following code:

BigDecimal bigDecimal = new BigDecimal(    new BigInteger(storedUnscaledBytes),    storedScale);

This approach stores values with arbitrary precision and a portablerepresentation, but the values are not human-readable in the database, and allcalculations must be performed by the application.

Store application internal representation as bytes

Another option is to serialize the arbitrary precision decimal values to bytearrays using the application's internal representation, then store them directlyin the database.

The stored database values are not human-readable, and the application needs toperform all calculations.

This approach has portability issues. If you try to read the values with aprogramming language or library different from the one that originally wrote it,it might not work. Reading the values back might not work because differentarbitrary precision libraries can have different serialized representations forbyte arrays.

What's next

• Read about otherdata typesavailable for Spanner.
• Learn how to correctly set up a Spannerschema design anddata model.
• Learn aboutoptimizing your schema design forSpanner.