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.
CockroachDB does not support nested arrays, creating database indexes on arrays, and ordering by arrays.
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
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
For a complete list of array functions built into CockroachDB, see the documentation on array functions.
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
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)
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:
> SELECT ARRAY[true,false,true]::INT[];
array
+---------+
{1,0,1}
(1 row)
> SELECT ARRAY[true,false,true]::TIMESTAMP[];
pq: invalid cast: bool[] -> TIMESTAMP[]
You can cast an array to a STRING value, for compatibility with PostgreSQL:
> SELECT ARRAY[1,NULL,3]::string;
array
+------------+
{1,NULL,3}
(1 row)
> SELECT ARRAY[(1,'a b'),(2,'c"d')]::string;
array
+----------------------------------+
{"(1,\"a b\")","(2,\"c\"\"d\")"}
(1 row)
See also
Was this helpful?
