Array(t)

An array of T-type items. T can be any data type, including an array.

Creating an Array

You can use a function to create an array:

array(T)

You can also use square brackets.

[]

Example of creating an array:

SELECT array(1, 2) AS x, toTypeName(x)

┌─x─────┬─toTypeName(array(1, 2))─┐
│ [1,2] │ Array(UInt8)            │
└───────┴─────────────────────────┘

SELECT [1, 2] AS x, toTypeName(x)

┌─x─────┬─toTypeName([1, 2])─┐
│ [1,2] │ Array(UInt8)       │
└───────┴────────────────────┘

Working with Data Types

The maximum size of an array is limited to one million elements.

When creating an array on the fly, ClickHouse automatically defines the argument type as the narrowest data type that can store all the listed arguments. If there are any Nullable or literal NULL values, the type of an array element also becomes Nullable.

If ClickHouse couldn’t determine the data type, it generates an exception. For instance, this happens when trying to create an array with strings and numbers simultaneously (SELECT array(1, 'a')).

Examples of automatic data type detection:

SELECT array(1, 2, NULL) AS x, toTypeName(x)

┌─x──────────┬─toTypeName(array(1, 2, NULL))─┐
│ [1,2,NULL] │ Array(Nullable(UInt8))        │
└────────────┴───────────────────────────────┘

If you try to create an array of incompatible data types, ClickHouse throws an exception:

SELECT array(1, 'a')

Received exception from server (version 1.1.54388):
Code: 386. DB::Exception: Received from localhost:9000, 127.0.0.1. DB::Exception: There is no supertype for types UInt8, String because some of them are String/FixedString and some of them are not.

Original article