ARRAY type

An array T[] is an ordered list of zero or more elements that share the same data type. PostgreSQL uses one-based indexing for arrays, meaning an array with n elements starts at array[1] and ends at array[n]. RisingWave also applies one-based indexing to align with PostgreSQL.

Define an array

To define an array in a schema, append [] to the data type of the column when you define the schema. For example, you can use trip_id VARCHAR[] to create an array that stores trip IDs.

You can also define a temporary array in an SQL statement in this syntax:

ARRAY[value1, value2, ...]

Examples

The following statement defines a temporary array and retrieves the columns in it.

SELECT ARRAY['foo', 'bar', null];
-----Result
{foo,bar,NULL}

The following statement defines a table x that has an array of arrays.

CREATE TABLE x (a INT[][]);

The following statement defines a table taxi that contains an array trip_id.

CREATE TABLE taxi (
        taxi_id VARCHAR,
        trip_id VARCHAR[],
        plate VARCHAR,
        company VARCHAR,
        license_expiration_date DATE,
        licensed_to VARCHAR
    );

Add values to an array

To add values to an array, in the SQL statement, use ARRAY to indicate that this is an array, and then enclose the data in the array with []. For example, ARRAY ['ABCD1234', 'ABCD1235', 'ABCD1236', 'ABCD1237'].

Examples

Add values to table x:

INSERT INTO x VALUES (ARRAY[ARRAY[1], ARRAY[2,3]]);

Add values to table taxi:

INSERT INTO taxi VALUES
        (
            'FAST0001',
            ARRAY['ABCD1234', 'ABCD1235', 'ABCD1236', 'ABCD1237'],
            'N5432N',
            'FAST TAXI',
            '2030-12-31',
            'DAVID WANG'
        );

Retrieve data in an array

To retrieve data in an array, use the ARRAY_COLUMN[RELATIVE_POSITION] syntax. Relative positions start from 1. For example, to access ABCD1234, the first object in the trip_id array, we can specify trip_id[1].

Examples

Retrieve the second element in array a from the x table.

SELECT a[2] FROM x;
-----Result
{2,3}

Retrieve the first element in the array trip_id from the taxi table.

SELECT trip_id[1]
FROM taxi;
-----Result
'ABCD1234'

Retrieve a slice of an array

To retrieve data in an array, use the ARRAY_COLUMN[n:m] syntax, where n and m are integers representing indices and are both inclusive. Either n, m, or both can be omitted. Relative positions start from 1. In multidimensional arrays, arrays with unmatching dimensions are allowed.

Examples

Retrieve the entire array with n omitted.

SELECT array[1,NULL,2][:3];
----Result
{1,NULL,2}

Retrieve the first two elements from a multidimensional array.

SELECT array[array[1],array[2],array[3]][-21432315:134124523][1:2];
----
{{1},{2}}

Differences from PostgreSQL

In RisingWave, assume arr is of type T[ ][ ][ ]:

• arr[x] is of type T[ ][ ]
• arr[x][y] is interpreted as [arr[x]](y), and of type T[ ]
• arr[x0:x1] is of type T[ ][ ][ ]
• arr[x0:x1][y0:y1] is interpreted as [arr[x0:x1]](y0:y1), and of type T[ ][ ][ ]
• arr[x0:x1][y] is interpreted as [arr[x0:x1]](y), and of type T[ ][ ]

In PostgreSQL, a 3-dimensional array arr is still of type T[ ]:

• arr[x] or arr[x][y] is of type T but due to an insufficient number of indices is of NULL value
• arr[x][y][z] is of type T
• arr[x0:x1][y0:y1][z0:z1] is of type T[ ] and 3-dimensional
• arr[x0:x1] is interpreted as arr[x0:x1][:][:], and of type T[ ] and 3-dimensional
• arr[x0:x1][y] is interpreted as arr[x0:x1][1:y][:], and of type T[ ] and 3-dimensional

Unnest data from an array

You can use the unnest() function to spread values in an array into separate rows.

SELECT unnest(array[1,2,3,4]);
------Result
      1
      2
      3
      4

Array functions and operators

For the full list of array functions and operators, see Array functions and operators.