GoogleSQL for BigQuery supports conversion. Conversion includes, but is not limited to, casting, coercion, and supertyping.
- Casting is explicit conversion and uses the
CAST()function. - Coercion is implicit conversion, which GoogleSQL performs automatically 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 as
PARSE_DATE(). To learn more about these functions, see
Conversion functions
Comparison of casting and coercion
The following table summarizes all possible cast and coercion possibilities for GoogleSQL data types. The Coerce to column applies to all expressions of a given data type, (for example, a column), but literals and parameters can also be coerced. See literal coercion and parameter coercion for details.
| From type | Cast to | Coerce to |
|---|---|---|
| INT64 | BOOL INT64 NUMERIC BIGNUMERIC FLOAT64 STRING |
NUMERIC BIGNUMERIC FLOAT64 |
| NUMERIC | INT64 NUMERIC BIGNUMERIC FLOAT64 STRING |
BIGNUMERIC FLOAT64 |
| BIGNUMERIC | INT64 NUMERIC BIGNUMERIC FLOAT64 STRING |
FLOAT64 |
| FLOAT64 | INT64 NUMERIC BIGNUMERIC FLOAT64 STRING |
|
| BOOL | BOOL INT64 STRING |
|
| STRING | BOOL INT64 NUMERIC BIGNUMERIC FLOAT64 STRING BYTES DATE DATETIME TIME TIMESTAMP |
|
| BYTES | STRING BYTES |
|
| DATE | STRING DATE DATETIME TIMESTAMP |
DATETIME |
| DATETIME | STRING DATE DATETIME TIME TIMESTAMP |
|
| TIME | STRING TIME |
|
| TIMESTAMP | STRING DATE DATETIME TIME TIMESTAMP |
|
| ARRAY | ARRAY | |
| STRUCT | STRUCT |
Casting
Most data types can be cast from one type to another with the CAST function.
When using CAST, a query can fail if GoogleSQL is unable to perform
the cast. If you want to protect your queries from these types of errors, you
can use SAFE_CAST. To learn more about the rules for CAST, SAFE_CAST and
other casting functions, see
Conversion functions.
Coercion
GoogleSQL coerces the result type of an argument expression to another
type if needed to match function signatures. For example, if function func()
is defined to take a single argument of type FLOAT64
and an expression is used as an argume