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.
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
> 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 & ConversionNew in v2.0
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[true, false, | | true]::INT[] | +--------------------------------+ | {1,0,1} | +--------------------------------+ (1 row) > SELECT ARRAY[true,false,true]::TIMESTAMP[]; pq: invalid cast: bool[] -> TIMESTAMP[]