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.
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:
The following statement defines a temporary array and retrieves the columns in it.
The following statement defines a table x
that has an array of arrays.
The following statement defines a table taxi
that contains an array trip_id
.
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']
.
Add values to table x
:
Add values to table taxi
:
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]
.
Retrieve the second element in array a
from the x
table.
Retrieve the first element in the array trip_id
from the taxi
table.
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.
Retrieve the entire array with n
omitted.
Retrieve the first two elements from a multidimensional array.
In RisingWave, assume arr
is of type T[ ][ ][ ]:
[arr[x]](y)
, and of type T[ ][arr[x0:x1]](y0:y1)
, and of type T[ ][ ][ ][arr[x0:x1]](y)
, and of type T[ ][ ]In PostgreSQL, a 3-dimensional array arr
is still of type T[ ]:
NULL
valueYou can use the unnest()
function to spread values in an array into separate rows.
For the full list of array functions and operators, see Array functions and operators.