ARRAY

On this page Carat arrow pointing down
Warning:
CockroachDB v2.1 is no longer supported as of April 30, 2020. For more details, refer to the Release Support Policy.

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

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

Creating an array column by adding the term ARRAY

icon/buttons/copy
> CREATE TABLE c (d INT ARRAY);
icon/buttons/copy
> INSERT INTO c VALUES (ARRAY[10,20,30]);
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.

icon/buttons/copy
> SELECT * FROM c;
+------------+
|     d      |
+------------+
| {10,20,30} |
+------------+
(1 row)
icon/buttons/copy
> SELECT d[2] FROM c;
+------+
| d[2] |
+------+
|   20 |
+------+
(1 row)

Appending an element to an array

Using the array_append function

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

Using the append (||) operator

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

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

New in v2.1: You can cast an array to a STRING value, for compatibility with PostgreSQL:

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

See also

Data Types


Yes No
On this page

Yes No