OceanBase Database allows you to use the CREATE TABLE statement to create array columns.
Considerations
You cannot set a default value for a column of the ARRAY type.
For the supported types of array elements, see Overview of array element types.
Examples
The syntax of the CREATE TABLE statement for creating a table with array columns is as follows:
CREATE TABLE t1(
c1 ARRAY(INT),
c2 ARRAY(VARCHAR(256)),
c3 ARRAY(BIGINT),
c4 ARRAY(TINYINT),
c5 ARRAY(FLOAT),
c6 ARRAY(DOUBLE),
c7 ARRAY(SMALLINT));
CREATE TABLE t2(
c1 INT[],
c2 VARCHAR(256)[],
c3 BIGINT[],
c4 TINYINT[],
c5 FLOAT[],
c6 DOUBLE[],
c7 SMALLINT[]);
Here is an example of defining a nested array:
CREATE TABLE t3(c1 ARRAY(ARRAY(INT)));
CREATE TABLE t4(c1 INT[][]);
A maximum of six nested layers are supported. Here is an example:
CREATE TABLE t5 (c1 INT[][][][][][]);
DESC t5;
+-------+-----------------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------------------------------+------+-----+---------+-------+
| c1 | ARRAY(ARRAY(ARRAY(ARRAY(ARRAY(ARRAY(INT)))))) | YES | | NULL | |
+-------+-----------------------------------------------+------+-----+---------+-------+
1 row in set
Here is an example of writing and querying array data.
Create a test table named t6.
obclient> CREATE TABLE t6(
c1 ARRAY(INT),
c2 ARRAY(VARCHAR(256)),
c3 ARRAY(BIGINT)
);
The return result is as follows:
obclient> DESC t6;
+-------+---------------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+------+---------+-------+
| c1 | ARRAY(INT) | YES | | NULL | |
| c2 | ARRAY(VARCHAR(256)) | YES | | NULL | |
| c3 | ARRAY(BIGINT) | YES | | NULL | |
+-------+---------------------+------+------+---------+-------+
3 rows in set
Insert data into the table.
// Call the `array()` function to construct an array object.
obclient> INSERT INTO t6 VALUES (ARRAY(1,2), ARRAY(1,2), ARRAY(1,2));
// Call the `[]` operator to construct an array object.
obclient> INSERT INTO t6 VALUES ([1,2], [1,2], [1,2]);
// Write array data by using formatted strings.
obclient> INSERT INTO t6 VALUES ("[1,2]", "[1,2]", "[1,2]");
A sample query result is as follows:
obclient> SELECT * FROM t6;
+-------+-----------+-------+
| c1 | c2 | c3 |
+-------+-----------+-------+
| [1,2] | ["1","2"] | [1,2] |
| [1,2] | ["1","2"] | [1,2] |
| [1,2] | ["1","2"] | [1,2] |
+-------+-----------+-------+
3 rows in set