# CREATE\_ARRAY

The `CREATE_ARRAY` command creates a new array of the specified length that is initialized with default values for the array’s inferred data type.

## Syntax

```
CREATE_ARRAY(length_expression)

```

## Arguments

**length\_expression**

An expression that evaluates to an integer value, which is used to specify the length of the array. If the expression does not evaluate to an integer or evaluates to a negative integer, an exception will be thrown.

## Remarks

The `CREATE_ARRAY()` function uses data type inference to determine the appropriate type for each element in the array. The function can be used in any expression where the data type of the array can be inferred, such as:

* In assignment statements, such as assigning the value of an array variable
* As an input parameter to functions that accept an array type
* In a `RETURN` statement for a function that returns an array type
* In expressions that use a valid operator for an array type, such as concatenation (`+`) or type casting (`:>`)

See the [Examples](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/create-array.md) section for examples of these expressions.

When `CREATE_ARRAY()` is executed, a new array is initialized and each element is given one of the following default values:

* If the array’s element data type is specified without a data type modifier, the default element values will be `NULL` for each index. For example, given `myarray ARRAY(INT) = CREATE_ARRAY(3);`, each of the three element values in the array will be `NULL`. Additionally, if the element data type has a `NULL` modifier, such as `myarray ARRAY(INT NULL) = CREATE_ARRAY(3)`, then each of the three element values in the array will be `NULL`.
* If the array’s element data type is specified with a `NOT NULL` modifier, the element values will be assigned the appropriate default value for the data type. For example, given `myarray ARRAY(INT NOT NULL) = CREATE_ARRAY(3)`, each of the three element values will be `0`, which is the default value for the `INT` data type. For a complete list of default values for each data type, see [Array Default Values](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/array.md).

## Examples

The following examples demonstrate how the type of an array can be inferred depending on the usage of `CREATE_ARRAY()`.

**Example: Variable Assignment**

The following snippet from a function creates a new integer array and assigns it to a variable:

```sql
DELIMITER //
CREATE FUNCTION example_func() RETURNS INT AS
  DECLARE
    a ARRAY(INT) = CREATE_ARRAY(10);
  BEGIN
    ...
  END //
DELIMITER ;

```

**Example: Input Parameter**

The following example demonstrates a function that accepts an array as an input parameter and returns its length.

```sql
DELIMITER //
CREATE OR REPLACE FUNCTION get_length(a ARRAY(INT)) RETURNS INT AS
  BEGIN
    RETURN LENGTH(a);
  END //
DELIMITER ;

```

```sql
SELECT get_length(CREATE_ARRAY(10));

```

```output

+------------------------------+
| get_length(CREATE_ARRAY(10)) |
+------------------------------+
|                           10 |
+------------------------------+
1 row in set (0.07 sec)

```

When this function is called by passing `CREATE_ARRAY()` as the input parameter, the type of the new array is inferred from the function definition’s input parameter type. In this case, the type is an integer array.

**Example: RETURN Statement**

The following example demonstrates two functions: one that simply creates a new array and returns it, and another which returns the size of the array. Note that you cannot directly return the result of `create_array_of_size()` in a `SELECT` statement because returning array type values to the client is not supported.

```sql
DELIMITER //
CREATE OR REPLACE FUNCTION create_array_of_size(a INT) RETURNS ARRAY(INT) AS
  BEGIN
    RETURN CREATE_ARRAY(a);
  END //

CREATE OR REPLACE FUNCTION get_length(b ARRAY(INT)) RETURNS INT AS
  BEGIN
    RETURN LENGTH(b);
  END //
DELIMITER ;

```

When the `create_array_of_size()` function is called, the returned array’s type is inferred from the function definition’s return type, which is an integer array.

```sql
SELECT get_length(create_array_of_size(10));

```

```output

+--------------------------------+
| get_length(get_array_size(10)) |
+--------------------------------+
|                             10 |
+--------------------------------+
1 row in set (0.10 sec)

```

**Example: Type Casting**

The following example demonstrates creation of an array, where its type is inferred from a typecast.

```sql
DELIMITER //
CREATE OR REPLACE FUNCTION get_length(a INT) RETURNS INT AS
  DECLARE
    b INT;
  BEGIN
    b = LENGTH(CREATE_ARRAY(a) :> ARRAY(INT));
    RETURN b;
  END //
DELIMITER ;

```

```sql
SELECT get_length(10);

```

```output

+----------------+
| get_length(10) |
+----------------+
|             10 |
+----------------+
1 row in set (0.12 sec)

```

**Example: Array Concatenation**

The following example demonstrates how to extend the length of an existing array by concatenating it with the result of `CREATE_ARRAY()`.

```sql
DELIMITER //
CREATE OR REPLACE FUNCTION extend_array(a ARRAY(INT NOT NULL)) RETURNS ARRAY(INT NOT NULL) AS
  DECLARE
    c ARRAY(INT NOT NULL) = a;
  BEGIN
    c += CREATE_ARRAY(5);
    RETURN c;
  END //

CREATE OR REPLACE FUNCTION list_array_elements(a ARRAY(INT NOT NULL)) RETURNS VARCHAR(255) AS
  DECLARE
    result VARCHAR(255) = "";
  BEGIN
    IF LENGTH(a) = 0 THEN
      RETURN "[]";
    END IF;
    result = CONCAT(result, "[", a[0]);
    FOR i IN 1..LENGTH(a) - 1 LOOP
      result = CONCAT(result, ", ", a[i]);
    END LOOP;
    result = CONCAT(result, "]");
    RETURN result;
  END //

CREATE OR REPLACE FUNCTION before_after_concat() RETURNS VARCHAR(255) AS
  DECLARE
    primes ARRAY(INT NOT NULL) = [2, 3, 5, 7, 11];
    result VARCHAR(255);
  BEGIN
    result = CONCAT("Original: ", list_array_elements(primes));
    primes = extend_array(primes);
    result = CONCAT(result, "; Updated: ", list_array_elements(primes));
    RETURN result;
  END //
DELIMITER ;

```

```sql
SELECT before_after_concat();

```

```output

+----------------------------------------------------------------------+
| before_after_concat()                                                |
+----------------------------------------------------------------------+
| Original: [2, 3, 5, 7, 11]; Updated: [2, 3, 5, 7, 11, 0, 0, 0, 0, 0] |
+----------------------------------------------------------------------+
1 row in set (0.00 sec)

```

**Related Topics**

* [ARRAY](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/array.md)

***

Modified at: June 11, 2026

Source: [/db/v9.1/reference/sql-reference/procedural-sql-reference/create-array/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/create-array/)

(An index of the documentation is available at /llms.txt)