Data Types

The engine supports a reduced set of types compared to full DBMS platforms.

Types

Name	Description
`ARRAY`	A list of items of the same type
`BOOLEAN`	Logical boolean (True/False).
`BLOB`	Variable-length binary data
`DOUBLE`	floating point number
`INTEGER`	Whole number
`DECIMAL`
`VARCHAR`	Variable-length character string
`STRUCT`	A dictionary of multiple named values, where each key is a string, but the value can be a different type for each key.
`DATE`	The date component of a TIMESTAMP
`TIME`	The time component of a TIMESTAMP
`TIMESTAMP`	Combination of date and time.
`INTERVAL`	The difference between two TIMESTAMP values

Note

Types marked with (DECIMAL, TIME, and INTERVAL) have limited support.

Casting

Functions

Values can be cast using the CAST function, its form is CAST(any AS type). Where values are incompatible, an error will be thrown, to avoid errors TRY_CAST (or SAFE_CAST) can be used instead which will return null instead of error.

Type Hints

Intervals

Intervals require definition by type hints, using the type name before providing a literal description of the value.

INTERVAL 'value' unit

Where unit can be 'Year', 'Month', 'Day', 'Hour', 'Minute' or 'Second'.

Example:

SELECT INTERVAL '1' YEAR

Other

BOOLEAN, DOUBLE, INTEGER and TIMESTAMP also support 'type hint' notation (e.g. SELECT TIMESTAMP '2022-01-01';) to perform casting.

BLOB supports b prefix notation (e.g. b'string')

Type Annotations

Some types support type annotations in the form <value>::<type>, for example 1::double is equivalent to 1.0.

Byte Strings

b prefixes can be used to mark string literals as byte strings. For example b'abc' is equivalent to blob('abc').

Coercion

Timestamps & Dates

Literal values in quotes may be in interpreted as a TIMESTAMP or DATE when they match a valid date in ISO 8601 format (e.g. YYYY-MM-DD and YYYY-MM-DD HH:MM). The decision regarding coercising to a DATE if there is no time component to the value, otherwise it will be coerced to a TIMESTAMP.

All TIMESTAMP and values read from datasets are coerced to nanosecond precision timestamps.

Numbers

Hex literals can be provided using 0x prefix, for example 0xc0ffee is handled as the integer 12648430.