CODEWITHSUNDEEP
cockroachdb
David
David

Reputation: 499

Array support in CockroachDB?

Can I store ARRAY column types (e.g. in Postgres) in CockroachDB?

Upvotes: 3

Views: 1219

Answers (3)

yoonghm
yoonghm

Reputation: 4645

As in 2022 Dec, CockroachDB support arrays - an ordered collection of values of the same type.

Creation of Array Columns

To create an array column in a CockroachDB table, we can use

  1. Appending square bracket ([]) to any non-array data type.
  2. Adding the term ARRAY to any non-array data type.

The size of an `ARRAY column is variable but it is recommended to keep value under 1 MB to ensure performance, otherwise performance may be affected.

CREATE TABLE my_table1 (
    id      INT PRIMARY KEY,
    numbers INT ARRAY
);

CREATE TABLE my_table2(
    id      INT PRIMARY KEY,
    numbers INT[]
);

Insertion of Records Into A Table with Array Column

We can insert arrays into thess tables using ARRAY[...] syntax.

INSERT INTO my_table1(id, numbers) VALUES(1, ARRAY[1,2,3]);
INSERT INTO my_table2(id, numbers) VALUES(1, ARRAY[4,5,6]);

Accessing All Values Or Individual Value from An Array Column

SELECT * from my_table1;

The output is:

  id | numbers
-----+----------
   1 | {1,2,3}
(1 row)

We use square bracket ([]) to access 1-indexed array column.

SELECT numbers[1] from my_table2;

The output is:

  id | numbers
-----+----------
   1 |       4

Accessing An Array Column with Containment Queries

The two containment operators are:

  1. <@ - is contained by
  2. @> - contains
SELECT * FROM my_table1 WHERE numbers <@ ARRAY[1,2,3];

The output is:

  id | numbers
-----+----------
   1 | {1,2,3}
(1 row)

SELECT * FROM my_table1 WHERE numbers <@ ARRAY[1,2,3,4];

The output is:

  id | numbers
-----+----------
   1 | {1,2,3}
(1 row)

SELECT * FROM my_table2 WHERE numbers @> ARRAY[4];

The output is:

  id | numbers
-----+----------
   1 | {4,5,6}
(1 row)

Appending An Element to An Array Column

UPDATE my_table1 SET numbers = array_append(numbers, 4) WHERE id = 1;
SELECT * from my_table1;

The output is:

  id |  numbers
-----+------------
   1 | {1,2,3,4}
(1 row)

See more from https://www.cockroachlabs.com/docs/stable/array.html

Upvotes: 0

Daniel Cremer
Daniel Cremer

Reputation: 446

CockroachDB now supports the ARRAY data type since v1.1 (released Oct 2017).

You can create an array column by adding square brackets to any data type or by using the term ARRAY

CREATE TABLE names (list STRING[]);
INSERT INTO names VALUES (ARRAY['Bertram', 'Wilberforce', 'Wooster']);
SELECT * FROM names;
+-------------------------------------+
|                list                 |
+-------------------------------------+
| {"Bertram","Wilberforce","Wooster"} |
+-------------------------------------+
(1 row)

Details can be found here in the docs: https://www.cockroachlabs.com/docs/stable/array.html

Upvotes: 4

David
David

Reputation: 499

CockroachDB doesn’t support arrays yet, but you can follow our progress on this GitHub issue.

Upvotes: 2

Related Questions