> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# ARRAY

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

The `ARRAY` data type stores one-dimensional, 1-indexed, homogeneous arrays of any non-array <InternalLink path="data-types">data type</InternalLink>.

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 supports indexing array columns with <InternalLink path="inverted-indexes">GIN indexes</InternalLink>. This permits accelerating containment queries (<InternalLink path="functions-and-operators#supported-operations">`@>`</InternalLink> and <InternalLink path="functions-and-operators#supported-operations">`<@`</InternalLink>) and overlap queries (<InternalLink path="functions-and-operators#supported-operations">`&&`</InternalLink>) on array columns by adding an index to them.

<Note>
  CockroachDB does not support nested arrays.
</Note>

## Syntax

A value of data type `ARRAY` can be expressed in the following ways:

* Appending square brackets (`[]`) to any non-array <InternalLink path="data-types">data type</InternalLink>.
* Adding the term `ARRAY` to any non-array <InternalLink path="data-types">data type</InternalLink>.

## 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, <InternalLink path="architecture/storage-layer#write-amplification">write amplification</InternalLink> and other considerations may cause significant performance degradation.

## Functions

For the list of supported `ARRAY` functions, see <InternalLink path="functions-and-operators">Functions and Operators</InternalLink>.

## Examples

### Creating an array column by appending square brackets

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE a (b STRING[]);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO a VALUES (ARRAY['sky', 'road', 'car']);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM a;
```

```
        b
------------------
  {sky,road,car}
(1 row)
```

### Creating an array column by adding the term `ARRAY`

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE c (d INT ARRAY);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO c VALUES (ARRAY[10,20,30]);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM c;
```

```
      d
--------------
  {10,20,30}
(1 row)
```

### Accessing an array element using array index

<Note>
  Arrays in CockroachDB are 1-indexed.
</Note>

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM c;
```

```
      d
--------------
  {10,20,30}
(1 row)
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT d[2] FROM c;
```

```
  d
------
  20
(1 row)
```

### Accessing an array column using containment queries

You can use the <InternalLink path="functions-and-operators#supported-operations">operators</InternalLink> `<@` ("is contained by") and `@>` ("contains") to run containment queries on `ARRAY` columns.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM c WHERE d <@ ARRAY[10,20,30,40,50];
```

```
      d
--------------
  {10,20,30}
(1 row)
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM c WHERE d @> ARRAY[10,20];
```

```
      d
--------------
  {10,20,30}
(1 row)
```

### Using the overlaps operator

You can use the `&&` (overlaps) <InternalLink path="functions-and-operators#supported-operations">operator</InternalLink> to select array columns by checking if another array overlaps the column array. Arrays overlap if they have any elements in common.

1. Create the table:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   CREATE TABLE a (b STRING[]);
   ```

2. Insert two new arrays:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   INSERT INTO a VALUES (ARRAY['runway', 'houses', 'city', 'clouds']);
   INSERT INTO a VALUES (ARRAY['runway', 'houses', 'city']);
   INSERT INTO a VALUES (ARRAY['sun','moon']);
   ```

3. Use the `&&` operator in a `WHERE` clause to a query:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   SELECT * FROM a WHERE b && ARRAY['clouds','moon'];
   ```

   ```
                     b
   -------------------------------
     {runway,houses,city,clouds}
     {sun,moon}
   (2 rows)

   Time: 30ms total (execution 2ms / network 28ms)
   ```

### Appending an element to an array

#### Using the `array_append` function

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM c;
```

```
      d
--------------
  {10,20,30}
(1 row)
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> UPDATE c SET d = array_append(d, 40) WHERE d[3] = 30;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM c;
```

```
        d
-----------------
  {10,20,30,40}
(1 row)
```

#### Using the append (`||`) operator

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM c;
```

```
        d
-----------------
  {10,20,30,40}
(1 row)
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> UPDATE c SET d = d || 50 WHERE d[4] = 40;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM c;
```

```
         d
--------------------
  {10,20,30,40,50}
(1 row)
```

### Ordering by an array

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE t (a INT ARRAY, b STRING);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO t VALUES (ARRAY[3,4],'threefour'),(ARRAY[1,2],'onetwo');
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM t;
```

```
    a   |     b
--------+------------
  {3,4} | threefour
  {1,2} | onetwo
(2 rows)
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM t ORDER BY a;
```

```
    a   |     b
--------+------------
  {1,2} | onetwo
  {3,4} | threefour
(2 rows)
```

## Supported casting and conversion

<InternalLink path="data-types#data-type-conversions-and-casts">Casting</InternalLink> 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:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT ARRAY[true,false,true]::INT[];
```

```
   array
-----------
  {1,0,1}
(1 row)
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT ARRAY[true,false,true]::TIMESTAMP[];
```

```
pq: invalid cast: bool[] -> TIMESTAMP[]
```

You can cast an array to a `STRING` value, for compatibility with PostgreSQL:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT ARRAY[1,NULL,3]::string;
```

```
    array
--------------
  {1,NULL,3}
(1 row)
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT ARRAY[(1,'a b'),(2,'c"d')]::string;
```

```
               array
------------------------------------
  {"(1,\"a b\")","(2,\"c\"\"d\")"}
(1 row)
```

### Implicit casting

CockroachDB supports implicit casting from string literals to arrays of all data types except the following:

* <InternalLink path="bytes">`BYTES`</InternalLink>
* <InternalLink path="enum">`ENUM`</InternalLink>
* <InternalLink path="jsonb">`JSONB`</InternalLink>
* <InternalLink path="serial">`SERIAL`</InternalLink>
* `Box2D` <InternalLink path="architecture/glossary#data-types">(spatial type)</InternalLink>
* `GEOGRAPHY` <InternalLink path="architecture/glossary#data-types">(spatial type)</InternalLink>
* `GEOMETRY` <InternalLink path="architecture/glossary#data-types">(spatial type)</InternalLink>

For example, if you create a table with a column of type `INT[]`:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE x (a UUID DEFAULT gen_random_uuid() PRIMARY KEY, b INT[]);
```

And then insert a string containing a comma-delimited set of integers contained in brackets:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO x(b) VALUES ('{1,2,3}'), (ARRAY[4,5,6]);
```

CockroachDB implicitly casts the string literal as an `INT[]`:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM x;
```

```
                   a                   |    b
---------------------------------------+----------
  2ec0ed91-8a82-4f2e-888e-ae86ece4fc60 | {4,5,6}
  a521d6e9-3a2a-490d-968c-1365cace038a | {1,2,3}
(2 rows)
```

## See also

* <InternalLink path="data-types">Data Types</InternalLink>
* <InternalLink path="inverted-indexes">GIN Indexes</InternalLink>