Arrays

An array is a collection of values of the same data type, which can include:

• Primitive types (e.g., INTEGER, TEXT, BOOLEAN)

• Objects

• Geographic types

Arrays can also be nested (arrays of arrays).

---

Defining Arrays

Arrays can be declared in table definitions using the ARRAY(<type>) syntax, for example:

CREATE TABLE my_table_arrays (
    tags ARRAY(TEXT),
    objects ARRAY(OBJECT AS (age INTEGER, name TEXT))
);

You can also use the shorthand <type>[]:

TEXT[]        -- equivalent to ARRAY(TEXT)
OBJECT[]      -- equivalent to ARRAY(OBJECT)

---

Inserting Array Values

Array literals are enclosed in square brackets []:

INSERT INTO my_table_arrays (tags, objects) VALUES (
    ['foo', 'bar'],
    [{"name" = 'Alice', "age" = 33}, {"name" = 'Bob', "age" = 45}]
);

Result:

+----------------+------------------------------------------------------------+
| tags           | objects                                                    |
+----------------+------------------------------------------------------------+
| ["foo", "bar"] | [{"age": 33, "name": "Alice"}, {"age": 45, "name": "Bob"}] |
+----------------+------------------------------------------------------------+

---

Array Syntax

Arrays can be written using:

• Short form: []

• Constructor form: ARRAY[...]

Examples:

[]                               -- empty array
[null]                           -- array with a null element
[1, 2, 3]                        -- integer array
['Zaphod', 'Ford', 'Arthur']     -- text array
ARRAY[true, false]               -- boolean array
ARRAY[column_a, column_b]        -- from columns
ARRAY[ARRAY[1, 2], ARRAY[3, 4]]  -- nested array

All elements in an array must share the same data type. If empty, the element type is inferred from the context.

---

PostgreSQL-Compatible String Literal Syntax

You can also define arrays using PostgreSQL’s string literal format:

SELECT '{ab, CD, "CD", null, "null"}'::ARRAY(TEXT) AS arr;

Result:

["ab", "CD", "CD", null, "null"]

• null (without quotes) → null value

• "null" (with quotes) → the string "null"

This form exists for PostgreSQL compatibility, but the bracket syntax is preferred.

---

Nested Arrays

You can define arrays of arrays directly:

CREATE TABLE SensorData (
    sensorID CHAR(10),
    readings ARRAY(ARRAY(DOUBLE))
);

They can also be passed to functions, for example:

CREATE FUNCTION sort_nested_array("data" ARRAY(ARRAY(DOUBLE)), sort_dimension SMALLINT)
RETURNS ARRAY(ARRAY(DOUBLE))
LANGUAGE JAVASCRIPT
AS '
function sort_nested_array(data, sort_dimension) {
    return data.sort((a, b) => a[sort_dimension] - b[sort_dimension]);
}';

Nested arrays can be built with ARRAY_AGG:

WITH sorteddata AS (
    SELECT sort_nested_array(ARRAY_AGG([ts, reading]), 0) AS nestedarray
    FROM metrics
)
SELECT (nestedarray[generate_series]::ARRAY(DOUBLE))[2] AS "Reading"
FROM generate_series(1, 2), sorteddata;

---

Notes:

• Accessing nested arrays can be slow, as it may require reading data directly from disk. If you use them often, consider restructuring into multiple tables.

• Nested arrays cannot be created dynamically (either as top-level columns or inside dynamic objects).