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.

New in v20.1: CockroachDB supports indexing array columns with inverted indexes. This permits accelerating containment queries (@> and <@) on array columns by adding an index to them.

Note:

CockroachDB does not support nested arrays or ordering by arrays.

Note:

Vectorized execution is currently not supported for this data type.

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 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:

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

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

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

Implicit casting to INT and DECIMAL ARRAYs

New in v20.1: CockroachDB supports implicit casting from string literals to INT and DECIMAL ARRAYs, where appropriate.

For example, if you create a table with a column of type INT[]:

copy
icon/buttons/copy
> CREATE TABLE x (a UUID DEFAULT gen_random_uuid() PRIMARY KEY, b INT[]);

And then insert a string containing a comma-delimited set of integers contained in brackets:

copy
icon/buttons/copy
> INSERT INTO x(b) VALUES ('{1,2,3}'), (ARRAY[4,5,6]);

CockroachDB implicitly casts the string literal as an INT[]:

copy
icon/buttons/copy
> SELECT * FROM x;
                   a                   |    b
---------------------------------------+----------
  2ec0ed91-8a82-4f2e-888e-ae86ece4fc60 | {4,5,6}
  a521d6e9-3a2a-490d-968c-1365cace038a | {1,2,3}
(2 rows)

See also



Yes No