This page provides an overview of all GoogleSQL for Bigtable data types, including information about their value domains. For information on data type literals and constructors, see Lexical Structure and Syntax.
Data type list
Name | Summary |
---|---|
Array type |
An ordered list of zero or more elements of non-array values. SQL type name: ARRAY
|
Boolean type |
A value that can be either TRUE or FALSE .SQL type name: BOOL
|
Bytes type |
Variable-length binary data. SQL type name: BYTES
|
Date type |
A Gregorian calendar date, independent of time zone. SQL type name: DATE
|
Enum type |
Named type that enumerates a list of possible values. SQL type name: ENUM
|
Map type |
Unordered collection of keys and associated values. SQL type name: MAP
|
Numeric types |
A numeric value. Several types are supported.
A 64-bit integer.
An approximate single precision numeric value.
An approximate double precision numeric value. |
String type |
Variable-length character data. SQL type name: STRING
|
Struct type |
Container of ordered fields. SQL type name: STRUCT
|
Timestamp type |
A timestamp value represents an absolute point in time,
independent of any time zone or convention such as
daylight saving time (DST). SQL type name: TIMESTAMP
|
Data type properties
When storing and querying data, it is helpful to keep the following data type properties in mind:
Nullable data types
For nullable data types, NULL
is a valid value. Currently, all existing
data types are nullable.
Orderable data types
Expressions of orderable data types can be used in an ORDER BY
clause.
Applies to all data types except for:
STRUCT
MAP
Ordering NULL
s
In the context of the ORDER BY
clause, NULL
s are the minimum
possible value; that is, NULL
s appear first in ASC
sorts.
To learn more about using ASC
, see
the ORDER BY
clause.
Ordering floating points
Floating point values are sorted in this order, from least to greatest:
NULL
NaN
— AllNaN
values are considered equal when sorting.-inf
- Negative numbers
- 0 or -0 — All zero values are considered equal when sorting.
- Positive numbers
+inf
Ordering arrays
ARRAY<T>
is orderable if its type, T
, is orderable. Empty arrays are
sorted before non-empty arrays. Non-empty arrays are sorted
lexicographically by element. An array that is a strict prefix of another array
orders less than the longer array.
Lexicographical ordering for arrays first compares the elements of each array from the first element to the last. If an element orders before a corresponding element in another array, then the arrays are ordered accordingly. Subsequent array elements are ignored.
For example:
Groupable data types
Can generally appear in an expression following GROUP BY
and DISTINCT
.
All data types are supported except for:
MAP
ARRAY
STRUCT
Comparable data types
Values of the same comparable data type can be compared to each other. All data types are supported except for:
MAP
ARRAY
Notes:
- Equality comparisons for structs are supported field by field, in field order. Field names are ignored. Less than and greater than comparisons are not supported.
Collatable data types
Collatable data types support collation, which determines how to sort and compare strings. These data types support collation:
- String
- String fields in a struct
- String elements in an array
Array type
Name | Description |
---|---|
ARRAY |
Ordered list of zero or more elements of any non-array type. |
An array is an ordered list of zero or more elements of non-array values. Elements in an array must share the same type.
Arrays of arrays are not allowed. Queries that would produce an array of
arrays will return an error. Instead, a struct must be inserted between the
arrays using the SELECT AS STRUCT
construct.
To learn more about the literal representation of an array type, see Array literals.
NULL
s and the array type
An empty array and a NULL
array are two distinct values. Arrays can contain
NULL
elements.
Declaring an array type
ARRAY<T>
Array types are declared using the angle brackets (<
and >
). The type
of the elements of an array can be arbitrarily complex with the exception that
an array cannot directly contain another array.
Examples
Type Declaration | Meaning |
---|---|
ARRAY<INT64>
|
Simple array of 64-bit integers. |
ARRAY<STRUCT<INT64, INT64>>
|
An array of structs, each of which contains two 64-bit integers. |
ARRAY<ARRAY<INT64>>
(not supported) |
This is an invalid type declaration which is included here just in case you came looking for how to create a multi-level array. Arrays cannot contain arrays directly. Instead see the next example. |
ARRAY<STRUCT<ARRAY<INT64>>>
|
An array of arrays of 64-bit integers. Notice that there is a struct between the two arrays because arrays cannot hold other arrays directly. |
Constructing an array
You can construct an array using array literals or array functions.
Using array literals
You can build an array literal in GoogleSQL using brackets ([
and
]
). Each element in an array is separated by a comma.
SELECT [1, 2, 3] AS numbers;
SELECT ["apple", "pear", "orange"] AS fruit;
SELECT [true, false, true] AS booleans;
You can also create arrays from any expressions that have compatible types.
To declare a specific data type for an array, use angle
brackets (<
and >
). For example:
SELECT ARRAY<FLOAT64>[1, 2, 3] AS floats;
Arrays of most data types, such as INT64
or STRING
, don't require
that you declare them first.
SELECT [1, 2, 3] AS numbers;
You can write an empty array of a specific type using ARRAY<type>[]
. You can
also write an untyped empty array using []
, in which case GoogleSQL
attempts to infer the array type from the surrounding context. If
GoogleSQL cannot infer a type, the default type ARRAY<INT64>
is used.
Using generated values
You can also construct an ARRAY
with generated values.
Generating arrays of integers
GENERATE_ARRAY
generates an array of values from a starting and ending value and a step value.
For example, the following query generates an array that contains all of the odd
integers from 11 to 33, inclusive:
SELECT GENERATE_ARRAY(11, 33, 2) AS odds;
/*--------------------------------------------------*
| odds |
+--------------------------------------------------+
| [11, 13, 15, 17, 19, 21, 23, 25, 27, 29, 31, 33] |
*--------------------------------------------------*/
You can also generate an array of values in descending order by giving a negative step value:
SELECT GENERATE_ARRAY(21, 14, -1) AS countdown;
/*----------------------------------*
| countdown |
+----------------------------------+
| [21, 20, 19, 18, 17, 16, 15, 14] |
*----------------------------------*/
Generating arrays of dates
GENERATE_DATE_ARRAY
generates an array of DATE
s from a starting and ending DATE
and a step
INTERVAL
.
You can generate a set of DATE
values using GENERATE_DATE_ARRAY
. For
example, this query returns the current DATE
and the following
DATE
s at 1 WEEK
intervals up to and including a later DATE
:
SELECT
GENERATE_DATE_ARRAY('2017-11-21', '2017-12-31', INTERVAL 1 WEEK)
AS date_array;
/*--------------------------------------------------------------------------*
| date_array |
+--------------------------------------------------------------------------+
| [2017-11-21, 2017-11-28, 2017-12-05, 2017-12-12, 2017-12-19, 2017-12-26] |
*--------------------------------------------------------------------------*/
Boolean type
Name | Description |
---|---|
BOOL |
Boolean values are represented by the keywords TRUE and
FALSE (case-insensitive). |
Boolean values are sorted in this order, from least to greatest:
NULL
FALSE
TRUE
Bytes type
Name | Description |
---|---|
BYTES |
Variable-length binary data. |
String and bytes are separate types that cannot be used interchangeably. Most functions on strings are also defined on bytes. The bytes version operates on raw bytes rather than Unicode characters. Casts between string and bytes enforce that the bytes are encoded using UTF-8.
To learn more about the literal representation of a bytes type, see Bytes literals.
Date type
Name | Range |
---|---|
DATE |
0001-01-01 to 9999-12-31. |
The date type represents a Gregorian calendar date, independent of time zone. A date value does not represent a specific 24-hour time period. Rather, a given date value represents a different 24-hour period when interpreted in different time zones, and may represent a shorter or longer day during daylight saving time (DST) transitions. To represent an absolute point in time, use a timestamp.
Canonical format
YYYY-[M]M-[D]D
YYYY
: Four-digit year.[M]M
: One or two digit month.[D]D
: One or two digit day.
To learn more about the literal representation of a date type, see Date literals.
Enum type
Name | Description |
---|---|
ENUM |
Named type that maps string constants to integer constants. |
An enum is a named type that enumerates a list of possible values, each of which contains:
- An integer value: Integers are used for comparison and ordering enum values. There is no requirement that these integers start at zero or that they be contiguous.
- A string value for its name: Strings are case sensitive. In the case of protocol buffer open enums, this name is optional.
- Optional alias values: One or more additional string values that act as aliases.
Enum values are referenced using their integer value or their string value. You reference an enum type, such as when using CAST, by using its fully qualified name.
You cannot create new enum types using GoogleSQL.
To learn more about the literal representation of an enum type, see Enum literals.
Map type
Name | Description |
---|---|
MAP |
Unordered collection of keys and associated values. |
Declaring a map type
MAP<KEY_TYPE, VALUE_TYPE>
To declare a map type, you must include angle brackets (<
and >
) and
the data types for the map keys and values.
KEY_TYPE
: The data type for the key. Groupable data types are allowed.VALUE_TYPE
: The data type for the key's associated value. All data types are allowed.
Numeric types
Numeric types include the following types:
INT64
FLOAT32
FLOAT64
Integer type
Integers are numeric values that do not have fractional components.
Name | Range |
---|---|
INT64
|
-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 |
To learn more about the literal representation of an integer type, see Integer literals.
Floating point types
Floating point values are approximate numeric values with fractional components.
Name | Description |
---|---|
FLOAT32
|
Single precision (approximate) numeric values. |
FLOAT64
|
Double precision (approximate) numeric values. |
To learn more about the literal representation of a floating point type, see Floating point literals.
Floating point semantics
When working with floating point numbers, there are special non-numeric values
that need to be considered: NaN
and +/-inf
Arithmetic operators provide standard IEEE-754 behavior for all finite input values that produce finite output and for all operations for which at least one input is non-finite.
Function calls and operators return an overflow error if the input is finite
but the output would be non-finite. If the input contains non-finite values, the
output can be non-finite. In general functions do not introduce NaN
s or
+/-inf
. However, specific functions like IEEE_DIVIDE
can return non-finite
values on finite input. All such cases are noted explicitly in
Mathematical functions.
Floating point values are approximations.
- The binary format used to represent floating point values can only represent
a subset of the numbers between the most positive number and most
negative number in the value range. This enables efficient handling of a
much larger range than would be possible otherwise.
Numbers that are not exactly representable are approximated by utilizing a
close value instead. For example,
0.1
cannot be represented as an integer scaled by a power of2
. When this value is displayed as a string, it is rounded to a limited number of digits, and the value approximating0.1
might appear as"0.1"
, hiding the fact that the value is not precise. In other situations, the approximation can be visible. - Summation of floating point values might produce surprising results because
of limited precision. For example,
(1e30 + 1) - 1e30 = 0
, while(1e30 - 1e30) + 1 = 1.0
. This is because the floating point value does not have enough precision to represent(1e30 + 1)
, and the result is rounded to1e30
. This example also shows that the result of theSUM
aggregate function of floating points values depends on the order in which the values are accumulated. In general, this order is not deterministic and therefore the result is not deterministic. Thus, the resultingSUM
of floating point values might not be deterministic and two executions of the same query on the same tables might produce different results.
Mathematical function examples
Left Term | Operator | Right Term | Returns |
---|---|---|---|
Any value | + |
NaN |
NaN |
1.0 | + |
+inf |
+inf |
1.0 | + |
-inf |
-inf |
-inf |
+ |
+inf |
NaN |
Maximum FLOAT64 value |
+ |
Maximum FLOAT64 value |
Overflow error |
Minimum FLOAT64 value |
/ |
2.0 | 0.0 |
1.0 | / |
0.0 |
"Divide by zero" error |
Comparison operators provide standard IEEE-754 behavior for floating point input.
Comparison operator examples
Left Term | Operator | Right Term | Returns |
---|---|---|---|
NaN |
= |
Any value | FALSE |
NaN |
< |
Any value | FALSE |
Any value | < |
NaN |
FALSE |
-0.0 | = |
0.0 | TRUE |
-0.0 | < |
0.0 | FALSE |
For more information on how these values are ordered and grouped so they can be compared, see Ordering floating point values.
String type
Name | Description |
---|---|
STRING |
Variable-length character (Unicode) data. |
Input string values must be UTF-8 encoded and output string values will be UTF-8 encoded. Alternate encodings like CESU-8 and Modified UTF-8 are not treated as valid UTF-8.
All functions and operators that act on string values operate on Unicode
characters rather than bytes. For example, functions like SUBSTR
and LENGTH
applied to string input count the number of characters, not bytes.
Each Unicode character has a numeric value called a code point assigned to it. Lower code points are assigned to lower characters. When characters are compared, the code points determine which characters are less than or greater than other characters.
Most functions on strings are also defined on bytes. The bytes version operates on raw bytes rather than Unicode characters. Strings and bytes are separate types that cannot be used interchangeably. There is no implicit casting in either direction. Explicit casting between string and bytes does UTF-8 encoding and decoding. Casting bytes to string returns an error if the bytes are not valid UTF-8.
To learn more about the literal representation of a string type, see String literals.
Struct type
Name | Description |
---|---|
STRUCT |
Container of ordered fields each with a type (required) and field name (optional). |
To learn more about the literal representation of a struct type, see Struct literals.
Declaring a struct type
STRUCT<T>
Struct types are declared using the angle brackets (<
and >
). The type of
the elements of a struct can be arbitrarily complex.
Examples
Type Declaration | Meaning |
---|---|
STRUCT<INT64>
|
Simple struct with a single unnamed 64-bit integer field. |
STRUCT<x STRUCT<y INT64, z INT64>>
|
A struct with a nested struct named x inside it. The struct
x has two fields, y and z , both of which
are 64-bit integers. |
STRUCT<inner_array ARRAY<INT64>>
|
A struct containing an array named inner_array that holds
64-bit integer elements. |
Constructing a struct
Tuple syntax
(expr1, expr2 [, ... ])
The output type is an anonymous struct type with anonymous fields with types matching the types of the input expressions. There must be at least two expressions specified. Otherwise this syntax is indistinguishable from an expression wrapped with parentheses.
Examples
Syntax | Output Type | Notes |
---|---|---|
(x, x+y) |
STRUCT<?,?> |
If column names are used (unquoted strings), the struct field data type is
derived from the column data type. x and y are
columns, so the data types of the struct fields are derived from the column
types and the output type of the addition operator. |
This syntax can also be used with struct comparison for comparison expressions
using multi-part keys, e.g., in a WHERE
clause:
WHERE (Key1,Key2) IN ( (12,34), (56,78) )
Typeless struct syntax
STRUCT( expr1 [AS field_name] [, ... ])
Duplicate field names are allowed. Fields without names are considered anonymous
fields and cannot be referenced by name. struct values can be NULL
, or can
have NULL
field values.
Examples
Syntax | Output Type |
---|---|
STRUCT(1,2,3) |
STRUCT<int64,int64,int64> |
STRUCT() |
STRUCT<> |
STRUCT('abc') |
STRUCT<string> |
STRUCT(1, t.str_col) |
STRUCT<int64, str_col string> |
STRUCT(1 AS a, 'abc' AS b) |
STRUCT<a int64, b string> |
STRUCT(str_col AS abc) |
STRUCT<abc string> |
Typed struct syntax
STRUCT<[field_name] field_type, ...>( expr1 [, ... ])
Typed syntax allows constructing structs with an explicit struct data type. The
output type is exactly the field_type
provided. The input expression is
coerced to field_type
if the two types are not the same, and an error is
produced if the types are not compatible. AS alias
is not allowed on the input
expressions. The number of expressions must match the number of fields in the
type, and the expression types must be coercible or literal-coercible to the
field types.
Examples
Syntax | Output Type |
---|---|
STRUCT<int64>(5) |
STRUCT<int64> |
STRUCT<date>("2011-05-05") |
STRUCT<date> |
STRUCT<x int64, y string>(1, t.str_col) |
STRUCT<x int64, y string> |
STRUCT<int64>(int_col) |
STRUCT<int64> |
STRUCT<x int64>(5 AS x) |
Error - Typed syntax does not allow AS |
Limited comparisons for structs
Structs can be directly compared using equality operators:
- Equal (
=
) - Not Equal (
!=
or<>
) - [
NOT
]IN
Notice, though, that these direct equality comparisons compare the fields of the struct pairwise in ordinal order ignoring any field names. If instead you want to compare identically named fields of a struct, you can compare the individual fields directly.
Timestamp type
Name | Range |
---|---|
TIMESTAMP |
0001-01-01 00:00:00 to 9999-12-31 23:59:59.999999 UTC |
A timestamp value represents an absolute point in time, independent of any time zone or convention such as daylight saving time (DST), with microsecond precision.
A timestamp is typically represented internally as the number of elapsed microseconds since a fixed initial point in time.
Note that a timestamp itself does not have a time zone; it represents the same instant in time globally. However, the display of a timestamp for human readability usually includes a Gregorian date, a time, and a time zone, in an implementation-dependent format. For example, the displayed values "2020-01-01 00:00:00 UTC", "2019-12-31 19:00:00 America/New_York", and "2020-01-01 05:30:00 Asia/Kolkata" all represent the same instant in time and therefore represent the same timestamp value.
- To represent a Gregorian date as it might appear on a calendar (a civil date), use a date value.
Canonical format
The canonical format for a timestamp literal has the following parts:
{
civil_date_part[time_part [time_zone]] |
civil_date_part[time_part[time_zone_offset]] |
civil_date_part[time_part[utc_time_zone]]
}
civil_date_part:
YYYY-[M]M-[D]D
time_part:
{ |T|t}[H]H:[M]M:[S]S[.F]
YYYY
: Four-digit year.[M]M
: One or two digit month.[D]D
: One or two digit day.{ |T|t}
: A space or aT
ort
separator. TheT
andt
separators are flags for time.[H]H
: One or two digit hour (valid values from 00 to 23).[M]M
: One or two digit minutes (valid values from 00 to 59).[S]S
: One or two digit seconds (valid values from 00 to 60).[.F]
: Up to six fractional digits (microsecond precision).[time_zone]
: String representing the time zone. When a time zone is not explicitly specified, the default time zone, UTC, is used. For details, see time zones.[time_zone_offset]
: String representing the offset from the Coordinated Universal Time (UTC) time zone. For details, see time zones.[utc_time_zone]
: String representing the Coordinated Universal Time (UTC), usually the letterZ
orz
. For details, see time zones.
To learn more about the literal representation of a timestamp type, see Timestamp literals.
Time zones
A time zone is used when converting from a civil date or time (as might appear on a calendar or clock) to a timestamp (an absolute time), or vice versa. This includes the operation of parsing a string containing a civil date and time like "2020-01-01 00:00:00" and converting it to a timestamp. The resulting timestamp value itself does not store a specific time zone, because it represents one instant in time globally.
Time zones are represented by strings in one of these canonical formats:
- Offset from Coordinated Universal Time (UTC), or the letter
Z
orz
for UTC. - Time zone name from the tz database.
The following timestamps are identical because the time zone offset
for America/Los_Angeles
is -08
for the specified date and time.
SELECT UNIX_MILLIS(TIMESTAMP '2008-12-25 15:30:00 America/Los_Angeles') AS millis;
SELECT UNIX_MILLIS(TIMESTAMP '2008-12-25 15:30:00-08:00') AS millis;
Specify Coordinated Universal Time (UTC)
You can specify UTC using the following suffix:
{Z|z}
You can also specify UTC using the following time zone name:
{Etc/UTC}
The Z
suffix is a placeholder that implies UTC when converting an RFC
3339-format value to a TIMESTAMP
value. The value Z
isn't
a valid time zone for functions that accept a time zone. If you're specifying a
time zone, or you're unsure of the format to use to specify UTC, we recommend
using the Etc/UTC
time zone name.
The Z
suffix isn't case sensitive. When using the Z
suffix, no space is
allowed between the Z
and the rest of the timestamp. The following are
examples of using the Z
suffix and the Etc/UTC
time zone name:
SELECT TIMESTAMP '2014-09-27T12:30:00.45Z'
SELECT TIMESTAMP '2014-09-27 12:30:00.45z'
SELECT TIMESTAMP '2014-09-27T12:30:00.45 Etc/UTC'
Specify an offset from Coordinated Universal Time (UTC)
You can specify the offset from UTC using the following format:
{+|-}H[H][:M[M]]
Examples:
-08:00
-8:15
+3:00
+07:30
-7
When using this format, no space is allowed between the time zone and the rest of the timestamp.
2014-09-27 12:30:00.45-8:00
Time zone name
Format:
tz_identifier
A time zone name is a tz identifier from the tz database. For a less comprehensive but simpler reference, see the List of tz database time zones on Wikipedia.
Examples:
America/Los_Angeles
America/Argentina/Buenos_Aires
Etc/UTC
Pacific/Auckland
When using a time zone name, a space is required between the name and the rest of the timestamp:
2014-09-27 12:30:00.45 America/Los_Angeles
Note that not all time zone names are interchangeable even if they do happen to
report the same time during a given part of the year. For example,
America/Los_Angeles
reports the same time as UTC-7:00
during daylight
saving time (DST), but reports the same time as UTC-8:00
outside of DST.
If a time zone is not specified, the default time zone value is used.
Leap seconds
A timestamp is simply an offset from 1970-01-01 00:00:00 UTC, assuming there are exactly 60 seconds per minute. Leap seconds are not represented as part of a stored timestamp.
If the input contains values that use ":60" in the seconds field to represent a leap second, that leap second is not preserved when converting to a timestamp value. Instead that value is interpreted as a timestamp with ":00" in the seconds field of the following minute.
Leap seconds do not affect timestamp computations. All timestamp computations are done using Unix-style timestamps, which do not reflect leap seconds. Leap seconds are only observable through functions that measure real-world time. In these functions, it is possible for a timestamp second to be skipped or repeated when there is a leap second.