Warning:
This version of CockroachDB is no longer supported. Cockroach Labs supports the current stable release and two releases prior. Please use one of these supported versions.

New in v1.1: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

copy
icon/buttons/copy
> CREATE TABLE a (b STRING[]);
copy
icon/buttons/copy
> INSERT INTO a VALUES (ARRAY['sky', 'road', 'car']);
copy
icon/buttons/copy
> SELECT * FROM a;
+----------------------+
|          b           |
+----------------------+
| {"sky","road","car"} |
+----------------------+
(1 row)

Creating an array column by adding the term ARRAY

copy
icon/buttons/copy
> CREATE TABLE c (d INT ARRAY);
copy
icon/buttons/copy
> INSERT INTO c VALUES (ARRAY[10,20,30]);
copy
icon/buttons/copy
> SELECT * FROM c;
+------------+
|     d      |
+------------+
| {10,20,30} |
+------------+
(1 row)

Accessing an array element using array index

Note:
Arrays in CockroachDB are 1-indexed.
copy
icon/buttons/copy
> SELECT * FROM c;
+------------+
|     d      |
+------------+
| {10,20,30} |
+------------+
(1 row)
copy
icon/buttons/copy
> SELECT d[2] FROM c;
+------+
| d[2] |
+------+
|   20 |
+------+
(1 row)

Appending an element to an array

Using the array_append function

copy
icon/buttons/copy
> SELECT * FROM c;
+------------+
|     d      |
+------------+
| {10,20,30} |
+------------+
(1 row)
copy
icon/buttons/copy
> UPDATE c SET d = array_append(d, 40) WHERE d[3] = 30;
copy
icon/buttons/copy
> SELECT * FROM c;
+---------------+
|       d       |
+---------------+
| {10,20,30,40} |
+---------------+
(1 row)

Using the append (||) operator

copy
icon/buttons/copy
> SELECT * FROM c;
+---------------+
|       d       |
+---------------+
| {10,20,30,40} |
+---------------+
(1 row)
copy
icon/buttons/copy
> UPDATE c SET d = d || 50 WHERE d[4] = 40;
copy
icon/buttons/copy
> SELECT * FROM c;
+------------------+
|        d         |
+------------------+
| {10,20,30,40,50} |
+------------------+
(1 row)

Supported Casting & ConversionNew in v2.0

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:

copy
icon/buttons/copy
> SELECT ARRAY[true,false,true]::INT[];
+--------------------------------+
|       ARRAY[true, false,       |
|          true]::INT[]          |
+--------------------------------+
| {1,0,1}                        |
+--------------------------------+
(1 row)
copy
icon/buttons/copy
> SELECT ARRAY[true,false,true]::TIMESTAMP[];
pq: invalid cast: bool[] -> TIMESTAMP[]

See Also

Data Types



Yes No