Data types

This page provides an overview of all GoogleSQL for BigQuery data types, including information about their value domains. For information on data type literals and constructors, see Lexical Structure and Syntax.

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. Conditions apply for arrays.

Orderable data types

Expressions of orderable data types can be used in an ORDER BY clause. Applies to all data types except for:

  • ARRAY
  • STRUCT
  • GEOGRAPHY
  • JSON

Ordering NULLs

In the context of the ORDER BY clause, NULLs are the minimum possible value; that is, NULLs appear first in ASC sorts and last in DESC sorts.

NULL values can be specified as the first or last values for a column irrespective of ASC or DESC by using the NULLS FIRST or NULLS LAST modifiers respectively.

To learn more about using ASC, DESC, NULLS FIRST and NULLS LAST, see the ORDER BY clause.

Ordering floating points

Floating point values are sorted in this order, from least to greatest:

  1. NULL
  2. NaN — All NaN values are considered equal when sorting.
  3. -inf
  4. Negative numbers
  5. 0 or -0 — All zero values are considered equal when sorting.
  6. Positive numbers
  7. +inf

Groupable data types

Groupable data types can generally appear in an expression following GROUP BY, DISTINCT, and PARTITION BY. All data types are supported except for:

  • GEOGRAPHY
  • JSON
  • ARRAY
  • STRUCT

Grouping with floating point types

Groupable floating point types can appear in an expression following GROUP BY and DISTINCT. PARTITION BY expressions cannot include floating point types.

Special floating point values are grouped in the following way, including both grouping done by a GROUP BY clause and grouping done by the DISTINCT keyword:

  • NULL
  • NaN — All NaN values are considered equal when grouping.
  • -inf
  • 0 or -0 — All zero values are considered equal when grouping.
  • +inf

Comparable data types

Values of the same comparable data type can be compared to each other. All data types are supported except for:

  • GEOGRAPHY
  • JSON

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.
  • To compare geography values, use ST_Equals.
  • When comparing ranges, the lower bounds are compared. If the lower bounds are equal, the upper bounds are compared, instead.
  • When comparing ranges, NULL values are handled as follows:
    • NULL lower bounds are sorted before non-NULL lower bounds.
    • NULL upper bounds are sorted after non-NULL upper bounds.
    • If two bounds that are being compared are NULL, the comparison is TRUE.
    • An UNBOUNDED bound is treated as a NULL bound.
  • All types that support comparisons can be used in a JOIN condition. See JOIN Types for an explanation of join conditions.

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

The maximum size of a column value is 10MiB, which applies to scalar and array types.

Data type sizes

Use the following table to see the size in logical bytes for each supported data type.

Data type Size
ARRAY The sum of the size of its elements. For example, an array defined as (ARRAY<INT64>) that contains 4 entries is calculated as 32 logical bytes (4 entries x 8 logical bytes).
BIGNUMERIC 32 logical bytes
BOOL 1 logical byte
BYTES 2 logical bytes + the number of logical bytes in the value
DATE 8 logical bytes
DATETIME 8 logical bytes
FLOAT64 8 logical bytes
GEOGRAPHY 16 logical bytes + 24 logical bytes * the number of vertices in the geography type. To verify the number of vertices, use the ST_NumPoints function.
INT64 8 logical bytes
INTERVAL 16 logical bytes
JSON The number of logical bytes in UTF-8 encoding of the JSON-formatted string equivalent after canonicalization.
NUMERIC 16 logical bytes
RANGE 16 logical bytes
STRING 2 logical bytes + the UTF-8 encoded st