On this page 
Warning:
As of April 12, 2019, CockroachDB v1.1 is no longer supported. For more details, refer to the Release Support Policy.
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
ARRAYto 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
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)
See Also
Was this helpful?
