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

  1. > CREATE TABLE a (b STRING[]);
  1. > INSERT INTO a VALUES (ARRAY['sky', 'road', 'car']);
  1. > SELECT * FROM a;
  1. +----------------------+
  2. |          b           |
  3. +----------------------+
  4. | {"sky","road","car"} |
  5. +----------------------+
  6. (1 row)

Creating an array column by adding the term ARRAY

  1. > CREATE TABLE c (d INT ARRAY);
  1. > INSERT INTO c VALUES (ARRAY[10,20,30]);
  1. > SELECT * FROM c;
  1. +------------+
  2. |     d      |
  3. +------------+
  4. | {10,20,30} |
  5. +------------+
  6. (1 row)

Accessing an array element using array index

Note:

Arrays in CockroachDB are 1-indexed.

  1. > SELECT * FROM c;
  1. +------------+
  2. |     d      |
  3. +------------+
  4. | {10,20,30} |
  5. +------------+
  6. (1 row)
  1. > SELECT d[2] FROM c;
  1. +------+
  2. | d[2] |
  3. +------+
  4. |   20 |
  5. +------+
  6. (1 row)

Appending an element to an array

Using the array_append function

  1. > SELECT * FROM c;
  1. +------------+
  2. |     d      |
  3. +------------+
  4. | {10,20,30} |
  5. +------------+
  6. (1 row)
  1. > UPDATE c SET d = array_append(d, 40) WHERE d[3] = 30;
  1. > SELECT * FROM c;
  1. +---------------+
  2. |       d       |
  3. +---------------+
  4. | {10,20,30,40} |
  5. +---------------+
  6. (1 row)

Using the append (||) operator

  1. > SELECT * FROM c;
  1. +---------------+
  2. |       d       |
  3. +---------------+
  4. | {10,20,30,40} |
  5. +---------------+
  6. (1 row)
  1. > UPDATE c SET d = d || 50 WHERE d[4] = 40;
  1. > SELECT * FROM c;
  1. +------------------+
  2. |        d         |
  3. +------------------+
  4. | {10,20,30,40,50} |
  5. +------------------+
  6. (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:

  1. > SELECT ARRAY[true,false,true]::INT[];
  1.    array
  2. +---------+
  3.   {1,0,1}
  4. (1 row)
  1. > SELECT ARRAY[true,false,true]::TIMESTAMP[];
  1. pq: invalid cast: bool[] -> TIMESTAMP[]

You can cast an array to a STRING value, for compatibility with PostgreSQL:

  1. > SELECT ARRAY[1,NULL,3]::string;
  1.     array
  2. +------------+
  3.   {1,NULL,3}
  4. (1 row)
  1. > SELECT ARRAY[(1,'a b'),(2,'c"d')]::string;
  1.                array
  2. +----------------------------------+
  3.   {"(1,\"a b\")","(2,\"c\"\"d\")"}
  4. (1 row)

See also

Data Types

Was this page helpful?
YesNo