Array Type
Applies to: ✅ Data Cloud SQL ✅ Tableau Hyper API
An array is a sequential collection of elements in a single SQL value. Arrays can model dependent information with an inherent ordering in a single row. For example, time series data from longitudinal studies or embedding vectors from machine learning models.
Array type syntax:
The optional not null specifier indicates if array elements can be null. For example, array(integer not null) represents a range of non-null integer elements.
All array types have these properties.
- Arrays have variable length. Within a column, arrays aren’t required to have the same number of elements.
- Arrays are one-dimensional.
For a comprehensive overview of supported operations on arrays, see the section on Array Functions.
You can create arrays in two ways.
- By using the type constructor syntax
The constructor syntax consists of the keyword array followed by a comma-separated list of element SQL values surrounded by square brackets [...].
By using this syntax, the array type is inferred automatically. If the given list of elements doesn’t contain a null value, the element type is inferred as non-nullable.
- By using a cast from string data
An array string literal consists of a comma-separated list of element literals surrounded by curly braces {...}. The element literal syntax is identical to that of the respective atomic type.
For string array types, any upper or lower case variant of the element literal null is parsed as a null value. To specify the string “null”, escape the element literal by using double quotes. For example:
Arrays are strongly typed and use the array’s element type for all elements in the array. The element type is a defining part of the array’s overall type, meaning that array(integer) is a different type than array(boolean) or array(text). You can build an array from any atomic type supported by Data Cloud SQL.
Part of an array’s element type is the element’s nullability. For example, array(smallint) is different from array(smallint not null).
These four options represent different array types.
| Type | Array nullable? | Elements nullable? | Possible values |
|---|---|---|---|
array(integer) | ✅ | ✅ | {},{1,2,3},{1,2,null}, null |
array(integer not null) | ✅ | ❌ | {},{1,2,3},null |
array(integer) not null | ❌ | ✅ | {},{1,2,3},{1,2,null} |
array(integer not null) not null | ❌ | ❌ | {},{1,2,3} |
The inner nullability of an array type can be changed by casting, using the conventional cast syntax.
A cast from a non-nullable element type to its nullable counterpart always succeeds. The reverse direction succeeds if the array doesn’t contain null elements. Casts across element types aren’t supported. The only exception is casts from array to string types.
Non-nullable element types use less memory and turn on optimizations for certain array operations. Use the most “restrictive” element type possible based on the use case at hand.
Arrays are subject to limitations.
- The size of an array is limited to 4GB.
- Arrays can’t be nested.
- Arrays can’t be used as column types in non-temporary tables.
See the restrictions regarding array support in external formats.