ARRAY

On this page Carat arrow pointing down

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

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

Data Types


Yes No
On this page

Yes No