Legacy SQL data types
This document details the data types supported by BigQuery's legacy SQL query syntax. The preferred query syntax for BigQuery is GoogleSQL. For information on data types in GoogleSQL, see the GoogleSQL data types .
Legacy SQL data types
Your data can include the following data types:
- Imported BYTES data must be base64-encoded, except for Avro BYTES data, which BigQuery can read and convert.
- BYTES data read from a BigQuery table are base64-encoded, unless you export to Avro format, in which case the Avro bytes data type applies.
64-bit signed integer.
If you are using the BigQuery API to load an integer outside the range of [-2 53 +1, 2 53 -1] (in most cases, this means larger than 9,007,199,254,740,991), into an integer (INT64) column, you must pass it as a string to avoid data corruption. This issue is caused by a limitation on integer size in JSON/ECMAScript. For more information, see the Numbers section of RFC 7159 .
- CSV format:
1
or0
,true
orfalse
,t
orf
,yes
orno
, ory
orn
(all case-insensitive). - JSON format:
true
orfalse
(case-insensitive).
Exact numeric in legacy SQL
You can read NUMERIC or BIGNUMERIC values in non-modifying clauses such as SELECT list (with aliases)
, GROUP BY keys
, and pass-through fields in
window functions, and so on. However, any computation over NUMERIC or
BIGNUMERIC values, including comparisons, produces undefined results.
The following cast and conversion functions are supported in legacy SQL:
-
CAST(<numeric> AS STRING)
-
CAST(<bignumeric> AS STRING)
-
CAST(<string> AS NUMERIC)
-
CAST(<string> AS BIGNUMERIC)
Civil time in legacy SQL
You can read civil time data types—DATE, TIME, and
DATETIME—and process them with non-modifying operators such as SELECT list (with aliases)
, GROUP BY keys
, and pass-through fields in
window functions, etc. However, any other computation over civil time values,
including comparisons, produces undefined results.
The following casts and conversion functions are supported in legacy SQL:
-
CAST(<date> AS STRING)
-
CAST(<time> AS STRING)
-
CAST(<datetime> AS STRING)
-
CAST(<string> AS DATE)
-
CAST(<string> AS TIME)
-
CAST(<string> AS DATETIME)
In practice, legacy SQL interprets civil time values as integers, and operations on integers that you think are civil time values produce unexpected results.
To compute values using civil time data types, consider GoogleSQL , which supports all SQL operations on the DATE , DATETIME , and TIME data types.
What's next
- To set a field's data type using the API, see
schema.fields.type
. - For GoogleSQL data types, see data types .