Conversion rules in GoogleSQL
GoogleSQL for Spanner supports conversion.Conversion includes, but isn't limited to, casting, coercion, andsupertyping.
- Casting is explicit conversion and uses the
CAST()function. - Coercion is implicit conversion, which GoogleSQL performsautomatically under the conditions described below.
- A supertype is a common type to which two or more expressions can be coerced.
There are also conversions that have their own function names, such asPARSE_DATE(). To learn more about these functions, seeConversion functions.
Comparison of casting and coercion
The following table summarizes all possible cast and coercion possibilities forGoogleSQL data types. TheCoerce to column applies to allexpressions of a given data type, (for example, acolumn).
| From type | Cast to | Coerce to |
|---|---|---|
INT64 | BOOLINT64NUMERICFLOAT32FLOAT64STRINGENUM | NUMERICFLOAT64 |
NUMERIC | INT64NUMERICFLOAT32FLOAT64STRING | FLOAT64 |
FLOAT32 | INT64NUMERICFLOAT32FLOAT64STRING | FLOAT64 |
FLOAT64 | INT64NUMERICFLOAT32FLOAT64STRING | |
BOOL | BOOLINT64STRING | |
STRING | BOOLINT64NUMERICFLOAT32FLOAT64STRINGBYTESDATETIMESTAMPENUMPROTO | |
BYTES | STRINGBYTESPROTO | |
DATE | STRINGDATETIMESTAMP | |
TIMESTAMP | STRINGDATETIMESTAMP | |
ARRAY | ARRAY | |
ENUM | ENUM (with the sameENUM name)INT64STRING | ENUM (with the sameENUM name) |
STRUCT | STRUCT | |
PROTO | PROTO (with the samePROTO name)STRINGBYTES | PROTO (with the samePROTO name) |
Casting
Most data types can be cast from one type to another with theCAST function.When usingCAST, a query can fail if GoogleSQL is unable to performthe cast. If you want to protect your queries from these types of errors, youcan useSAFE_CAST. To learn more about the rules forCAST,SAFE_CAST andother casting functions, seeConversion functions.
Coercion
GoogleSQL coerces the result type of an argument expression to anothertype if needed to match function signatures. For example, if functionfunc()is defined to take a single argument of typeFLOAT64and an expression is used as an argument that has a result type ofINT64, then the result of the expression will becoerced toFLOAT64 type beforefunc() is computed.
Supertypes
A supertype is a common type to which two or more expressions can be coerced.Supertypes are used with set operations such asUNION ALL and expressions suchasCASE that expect multiple arguments with matching types. Each type has oneor more supertypes, including itself, which defines its set of supertypes.
| Input type | Supertypes |
|---|---|
BOOL | BOOL |
INT64 | INT64FLOAT32FLOAT64NUMERIC |
FLOAT32 | FLOAT32FLOAT64 |
FLOAT64 | FLOAT64 |
NUMERIC | NUMERICFLOAT64 |
STRING | STRING |
DATE | DATE |
TIMESTAMP | TIMESTAMP |
ENUM | ENUM with the same name. The resulting enum supertype is the one that occurred first. |
BYTES | BYTES |
STRUCT | STRUCT with the same field position types. |
ARRAY | ARRAY with the same element types. |
PROTO | PROTO with the same name. The resultingPROTO supertype is the one that occurred first. For example, the first occurrence could be in the first branch of a set operation or the first result expression in aCASE statement. |
GRAPH_ELEMENT | GRAPH_ELEMENT. A graph element can be a supertype of another graph element if the following is true:
|
GRAPH_PATH | GRAPH_PATH. A graph path can be a supertype of another graph path if the following is true:
|
If you want to find the supertype for a set of input types, first determine theintersection of the set of supertypes for each input type. If that set is emptythen the input types have no common supertype. If that set is non-empty, thenthe common supertype is generally themost specific type in that set. Generally,the most specific type is the type with the most restrictive domain.
Examples
| Input types | Common supertype | Returns | Notes |
|---|---|---|---|
INT64FLOAT32 | FLOAT64 | FLOAT64 | If you apply supertyping toINT64 andFLOAT32, supertyping succeeds because they they share a supertype,FLOAT64. |
INT64FLOAT64 | FLOAT64 | FLOAT64 | If you apply supertyping toINT64 andFLOAT64, supertyping succeeds because they they share a supertype,FLOAT64. |
INT64BOOL | None | Error | If you apply supertyping toINT64 andBOOL, supertyping fails because they don't share a common supertype. |
Exact and inexact types
Numeric types can be exact or inexact. For supertyping, if all of theinput types are exact types, then the resulting supertype can only be anexact type.
The following table contains a list of exact and inexact numeric data types.
| Exact types | Inexact types |
|---|---|
INT64NUMERIC | FLOAT32FLOAT64 |
Examples
| Input types | Common supertype | Returns | Notes |
|---|---|---|---|
INT64FLOAT64 | FLOAT64 | FLOAT64 | If supertyping is applied toINT64 andFLOAT64, supertyping succeeds because there are exact and inexact numeric types being supertyped. |
Types specificity
Each type has a domain of values that it supports. A type with anarrow domain is more specific than a type with a wider domain. Exact typesare more specific than inexact types because inexact types have a wider rangeof domain values that are supported than exact types. For example,INT64 is more specific thanFLOAT64.
Supertypes and literals
Supertype rules for literals are more permissive than for normal expressions,and are consistent with implicit coercion rules. The following algorithm is usedwhen the input set of types includes types related to literals:
- If there exists non-literals in the set, find the set of common supertypesof the non-literals.
- If there is at least one possible supertype, find themost specific type towhich the remaining literal types can be implicitly coerced and return thatsupertype. Otherwise, there is no supertype.
- If the set only contains types related to literals, compute the supertype ofthe literal types.
- If all input types are related to
NULLliterals, then the resultingsupertype isINT64. - If no common supertype is found, an error is produced.
Examples
| Input types | Common supertype | Returns |
|---|---|---|
INT64 literalUINT64 expression | UINT64 | UINT64 |
FLOAT64 literalFLOAT32 expression | FLOAT32 | FLOAT32 |
INT64 literalFLOAT64 literal | FLOAT64 | FLOAT64 |
TIMESTAMP expressionSTRING literal | TIMESTAMP | TIMESTAMP |
NULL literalNULL literal | INT64 | INT64 |
BOOL literalTIMESTAMP literal | None | Error |
Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.
Last updated 2025-12-15 UTC.