Spanner supports a NUMERIC
data type in both GoogleSQL and
PostgreSQL databases.
GoogleSQL NUMERIC
The GoogleSQL NUMERIC
is an
exact numeric data type capable of representing an exact numeric value with a
precision of 38 and scale of 9. This page provides an overview of how NUMERIC
is represented in client libraries.
PostgreSQL NUMERIC
The PostgreSQL NUMERIC
type is an arbitrary decimal precision numeric
data type with a maximum precision (total digits) of 147,455 and a maximum scale
(digits to the right of the decimal point) of 16,383.
Spanner DDL does not support specifying precision and scale for
PostgreSQL NUMERIC
columns. However, numeric values can be cast to
fixed precision values in DML statements. For example:
update
t1
set
numeric_column
=
(
numeric_column
*
0
.
8
)::
numeric
(
5
,
2
);
The type DECIMAL
is an alias for NUMERIC
.
PostgreSQL NUMERIC
columns cannot be used when specifying primary keys,
foreign keys, or secondary indexes.
Represent NUMERIC in each client library language
To maintain the fidelity of NUMERIC
values, each Spanner client
library
stores those values in an appropriate data type in the client
library language. The following table lists the data types to which NUMERIC
is
mapped in each supported language.
| Language | GoogleSQL | PostgreSQL |
|---|---|---|
|
C++
|
spanner::Numeric | |
|
C#
|
SpannerNumeric | |
|
Go
|
big.Rat | Custom PGNumeric |
|
Java
|
BigDecimal | Custom type. See PostgreSQL Java library notes . |
|
Node.js
|
Big | |
|
PHP
|
custom Numeric | |
|
Python
|
Decimal | Decimal with custom annotation |
|
Ruby
|
BigDecimal |
Three client libraries, C++, C# and PHP have each implemented a custom type to
represent Spanner SQL's NUMERIC
type. All other libraries use an
existing type.
The C++ client library spanner::Numeric
object does not support arithmetic
operations. Instead, convert the contained number to the C++ object of choice.
For example, you can extract the number as a string, which would represent the
number at full fidelity and with no data loss. If, however, you know in advance
that number fits, for example, within the range of std:int64_t
or double
,
then you can access the value as that type.
PostgreSQL Java library notes
The Spanner Java client library uses a custom Value.pgNumeric
type
to store PostgreSQL NUMERIC values.
Write to a NUMERIC column
Multiple types are supported when writing to a NUMERIC column in a PostgreSQL table.
-
Numerics
INSERT INTO Table (id, PgNumericColumn) VALUES (1, 1.23) -
Integers
INSERT INTO Table (id, PgNumericColumn) VALUES (1, 1) -
Doubles
INSERT INTO Table (id, PgNumericColumn) VALUES (1, 1.23::float8) -
Untyped literals
INSERT INTO Table (id, PgNumericColumn) VALUES (1, 'NaN')
Parameterized queries
When using parameterized queries, specify the parameters with $<index>
,
where <index>
denotes the parameter position. The parameter should then be
bound using p<index>
. For example, INSERT INTO MyTable (PgNumericColumn) VALUES ($1)
with the parameter being p1
.
The Java client library supports the following types as parameterized values:
-
Custom
Value.pgNumericStatement .newBuilder("INSERT INTO MyTable (PgNumericColumn) VALUES ($1), ($2)") .bind("p1") .to(Value.pgNumeric("1.23")) .bind("p2") .to(Value.pgNumeric("NaN")) .build() -
Doubles
Statement .newBuilder("INSERT INTO MyTable (PgNumericColumn) VALUES ($1), ($2)") .bind("p1") .to(1.23D) .bind("p2") .to(Double.NaN) .build() -
Integers
Statement .newBuilder("INSERT INTO MyTable (PgNumericColumn) VALUES ($1)") .bind("p1") .to(1) .build() -
Longs
Statement .newBuilder("INSERT INTO MyTable (PgNumericColumn) VALUES ($1)") .bind("p1") .to(1L) .build()
Mutations
When using Mutations, the following values are allowed to be written to columns of numeric type:
-
Strings
Mutation .newInsertBuilder("MyTable") .set("PgNumericColumn") .to("1.23") .build() -
Values of BigDecimal types
BigDecimals
Mutation .newInsertBuilder("MyTable") .set("PgNumericColumn") .to(new BigDecimal("1.23")) .build()Ints
Mutation .newInsertBuilder("MyTable") .set("PgNumericColumn") .to(1) .build()Longs
Mutation .newInsertBuilder("MyTable") .set("PgNumericColumn") .to(1L) .build() -
Values obtained as a result of a call to Value.pgNumeric
Mutation .newInsertBuilder("MyTable") .set("PgNumericColumn") .to(Value.pgNumeric("1.23")) .build()
Retrieve from a NUMERIC column
To obtain values stored in numeric columns of a ResultSet,
use ResultSet.getString()
or ResultSet.getValue()
.
-
Strings
resultSet.getString("PgNumericColumn") -
Custom Value
Value pgNumeric = resultSet.getValue("PgNumericColumn"); pgNumeric.getString(); // get underlying value as a String pgNumeric.getNumeric(); // get underlying value as a BigDecimal pgNumeric.getFloat64(); // get underlying value as aDouble
Add a NUMERIC column
The following sample shows how to add a NUMERIC
column to a table called Venues
using the Spanner client libraries.
C++
C#
Go
Java
Node.js
PHP
Python
Ruby
Update NUMERIC data
The following sample shows how to update NUMERIC
data using the Spanner client libraries.
C++
C#
Go
Java
Node.js
PHP
Python
Ruby
Query NUMERIC data
The following sample shows how to query NUMERIC
data using the Spanner client libraries.
C++
C#
Go
Java
Node.js
PHP
Python
Ruby
NUMERIC
is supported in the Spanner JDBC driver using the Java BigDecimal
type. For examples of how NUMERIC
is used, see the code samples in Connect JDBC to a GoogleSQL-dialect database
.
Handle NUMERIC when creating a client library or driver
The NUMERIC
type is encoded as a string in decimal or scientific notation
within a google.protobuf.Value
proto. This proto is wrapped as either a ResultSet
, PartialResultSet
, or a Mutation
depending on whether
it is being read or written. ResultSetMetadata will use the NUMERIC
TypeCode
to indicate that the corresponding value should be read as a NUMERIC
.
When working with NUMERIC in a client library or driver you create, observe the following guidance.
-
To read a
NUMERICfrom the ResultSet:-
Read the string_value from the google.protobuf.Value proto when TypeCode is
NUMERIC -
Convert that string to the relevant type for the given language
-
-
To write a
NUMERICusing Mutations, use the string representation as the string_value in the google.protobuf.Value proto when given the relevant type.
