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.
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[]
New in v2.1: 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?
