Create an array column

You can use the CREATE TABLE statement to create an array column in seekdb.

Considerations

• You cannot set a default value for an array column.

• For information about supported array types, see Overview of array element types.

Examples

You can use the CREATE TABLE statement to create a table with an array column. Here is the syntax:

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[][]);

You can nest arrays up to six levels. 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 (0.002 sec)

Here are examples of writing and querying array data.

Create a test table named t6:

CREATE TABLE t6(
    c1 ARRAY(INT),
    c2 ARRAY(VARCHAR(256)),
    c3 ARRAY(BIGINT)
);

The return result is as follows:

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 (0.002 sec)

Insert data:

// Construct an array object by using an array function.
INSERT INTO t6 VALUES (ARRAY(1,2), ARRAY(1,2), ARRAY(1,2));
// Construct an array object by using [].
INSERT INTO t6 VALUES ([1,2], [1,2], [1,2]);
// Write array data by using a formatted string.
INSERT INTO t6 VALUES ("[1,2]", "[1,2]", "[1,2]");

Query the test table. The result is as follows:

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 (0.002 sec)