Array(T)

Array of T-type items.

T can be anything, including an array.

Creating an array

You can use a function to create an array:

  1. array(T)

You can also use square brackets.

  1. []

Example of creating an array:

  1. SELECT array(1, 2) AS x, toTypeName(x)
  1. ┌─x─────┬─toTypeName(array(1, 2))─┐
  2. [1,2] Array(UInt8)
  3. └───────┴─────────────────────────┘
  1. SELECT [1, 2] AS x, toTypeName(x)
  1. ┌─x─────┬─toTypeName([1, 2])─┐
  2. [1,2] Array(UInt8)
  3. └───────┴────────────────────┘

Working with data types

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 NULL or Nullable type arguments, the type of array elements is Nullable.

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

Examples of automatic data type detection:

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

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

  1. SELECT array(1, 'a')
  1. Received exception from server (version 1.1.54388):
  2. 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.