# ARRAY

The `ARRAY` data type stores one-dimensional, 1-indexed, homogeneous arrays of any non-array data type.

The `ARRAY` data type is useful for ensuring compatibility with ORMs and other tools. However, if such compatibility is not a concern, it's more flexible to design your schema with normalized tables.

Note:

CockroachDB does not support nested arrays, creating database indexes on arrays, and ordering by arrays.

## Syntax

A value of data type `ARRAY` can be expressed in the following ways:

- Appending square brackets (`[]`) to any non-array data type.
- Adding the term `ARRAY` to any non-array data type.

## Size

The size of an `ARRAY` value is variable, but it's recommended to keep values under 1 MB to ensure performance. Above that threshold, write amplification and other considerations may cause significant performance degradation.

## Examples

Tip:

For a complete list of array functions built into CockroachDB, see the documentation on array functions.

### Creating an array column by appending square brackets

    > CREATE TABLE a (b STRING[]);

    > INSERT INTO a VALUES (ARRAY['sky', 'road', 'car']);

    > SELECT * FROM a;

    +----------------------+
    |          b           |
    +----------------------+
    | {"sky","road","car"} |
    +----------------------+
    (1 row)

### Creating an array column by adding the term `ARRAY`

    > CREATE TABLE c (d INT ARRAY);

    > INSERT INTO c VALUES (ARRAY[10,20,30]);

    > SELECT * FROM c;

    +------------+
    |     d      |
    +------------+
    | {10,20,30} |
    +------------+
    (1 row)

### Accessing an array element using array index

Note:

Arrays in CockroachDB are 1-indexed.

    > SELECT * FROM c;

    +------------+
    |     d      |
    +------------+
    | {10,20,30} |
    +------------+
    (1 row)

    > SELECT d[2] FROM c;

    +------+
    | d[2] |
    +------+
    |   20 |
    +------+
    (1 row)

### Appending an element to an array

#### Using the `array_append` function

    > SELECT * FROM c;

    +------------+
    |     d      |
    +------------+
    | {10,20,30} |
    +------------+
    (1 row)

    > UPDATE c SET d = array_append(d, 40) WHERE d[3] = 30;

    > SELECT * FROM c;

    +---------------+
    |       d       |
    +---------------+
    | {10,20,30,40} |
    +---------------+
    (1 row)

#### Using the append (`||`) operator

    > SELECT * FROM c;

    +---------------+
    |       d       |
    +---------------+
    | {10,20,30,40} |
    +---------------+
    (1 row)

    > UPDATE c SET d = d || 50 WHERE d[4] = 40;

    > SELECT * FROM c;

    +------------------+
    |        d         |
    +------------------+
    | {10,20,30,40,50} |
    +------------------+
    (1 row)

## Supported casting and conversion

Casting between `ARRAY` values is supported when the data types of the arrays support casting. For example, it is possible to cast from a `BOOL` array to an `INT` array but not from a `BOOL` array to a `TIMESTAMP` array:

    > SELECT ARRAY[true,false,true]::INT[];

       array
    +---------+
      {1,0,1}
    (1 row)

    > SELECT ARRAY[true,false,true]::TIMESTAMP[];

    pq: invalid cast: bool[] -> TIMESTAMP[]

New in v2.1: You can cast an array to a `STRING` value, for compatibility with PostgreSQL:

    > SELECT ARRAY[1,NULL,3]::string;

        array
    +------------+
      {1,NULL,3}
    (1 row)

    > SELECT ARRAY[(1,'a b'),(2,'c"d')]::string;

                   array
    +----------------------------------+
      {"(1,\"a b\")","(2,\"c\"\"d\")"}
    (1 row)

## See also

Data Types