Work with arrays ​

Arrays retain an ordered collection with one member type. Use explicit declarations when the element range or nullability must be enforced.

Create and read an array ​

SELECT array_element(array(10, 20, 30), -1),
       array_length(array(10, 20, 30))
FORMAT csv;

30,3

Element positions are one-based; negative positions count from the end. Zero is invalid. A missing position returns the declared member type's default. This differs from array_position, which returns NULL when a value is not found.

Expand into rows ​

Use unnest in FROM to expand the scalar leaves:

SELECT unnest AS value
FROM unnest(array(10, 20, 30))
ORDER BY unnest
FORMAT csv;

10
20
30

Collect rows ​

Array aggregation can restore an ordered collection and retain missing elements:

CREATE TABLE readings (value INTEGER);
INSERT INTO readings VALUES (20), (10), (NULL);

SELECT array_agg(value ORDER BY value NULLS LAST)
FROM readings
FORMAT csv;

"[10,20,null]"

Use Array(Nullable(T)) when typed input must admit NULL members. Bracket access and lambda expressions are not supported; use the documented functions.

See Array, array functions and array_agg.