Arrays

Columns can contain arrays, which store ordered lists of elements of the same type. For example, an address column could use an array of strings to store multiple addresses that an individual might have, such as ['668 SW New Lane', '518 Main Ave', '7040 Campfire Dr'].

There are two types of arrays:

• Native array: a one-dimensional array of a primitive type.

• Non-native array: all other supported arrays, including arrays that contain other arrays (multi-dimensional arrays) or structs (ROWs). Non-native arrays have some usage restrictions.

Use the ARRAY type to define an array column, specifying the type of its elements (a primitive type, a ROW (struct), or an array):

=> CREATE TABLE orders
   (orderkey    INT,
    custkey     INT,
    prodkey     ARRAY[VARCHAR(10)],
    orderprices ARRAY[DECIMAL(12,2)],
    orderdate   DATE
   );

If an array is multi-dimensional, represent it as an array containing an array:

ARRAY[ARRAY[FLOAT]]

Queries

See Arrays and sets (collections).

Restrictions

• Native arrays support only data of primitive types, for example, int, UUID, and so on.

• Array dimensionality is enforced. A column cannot contain arrays of varying dimensions. For example, a column that contains a three-dimensional array can only contain other three-dimensional arrays; it cannot simultaneously include a one-dimensional array. However, the arrays in a column can vary in size, where one array can contain four elements while another contains ten.

• Array bounds, if specified, are enforced for all operations that load or alter data. Unbounded arrays may have as many elements as will fit in the allotted binary size.

• An array has a maximum binary size. If this size is not set when the array is defined, a default value is used.

• Arrays do not support LONG types (like LONG VARBINARY or LONG VARCHAR) or user-defined types (like Geometry).