Overview
In Oxla, an array allows you to represent a collection of elements that have the same data type (any built-in data type can be used).
Currently, the implementation is limited only to single-dimensional arrays
Array Type Declaration
An array type can be declared by appending square brackets to the data type of its elements:
CREATE TABLE movie_night (
event_date DATE NOT NULL,
movies_planned TEXT[5] NOT NULL
);
The syntax above allows you to specify the size of the array. However, it does not enforce any limits and the behavior will be the same for arrays of unspecified length. There is also another way to declare an array, by prepending the ARRAY
keyword after the data type of the elements:
CREATE TABLE movie_night (
event_date DATE NOT NULL,
movies_planned TEXT ARRAY NOT NULL
);
Array Values
You can create array literals by using the ARRAY
keyword and combining it with the array’s values enclosed in square brackets and separated by commas:
ARRAY[ value1 , value2 , ... ]
Such a literal can be used with, e.g. SELECT
or INSERT INTO
statements:
SELECT ARRAY['10:14:25'::time, '22:58:11'::time];
?column?
---------------------
{10:14:25,22:58:11}
(1 row)
INSERT INTO movie_night VALUES
('2024-12-01', ARRAY['Inception', 'Interstellar', 'The Prestige']);
INSERT 0 1
SELECT * FROM movie_night;
event_date | movies_planned
------------+-----------------------------------------
2024-12-01 | {Inception,Interstellar,"The Prestige"}
(1 row)
You can also use a string representation of an array as another available option for array’s values syntax. It requires the elements’ values to be enclosed in curly braces and separated by commas:
'{ value1 , value2 , ... }'
Such an array value representation can be used in e.g. INSERT INTO
statements with the VALUES
clause:
INSERT INTO movie_night VALUES ('2024-12-15', '{The Matrix, John Wick}');
INSERT 0 1
SELECT * FROM movie_night;
event_date | movies_planned
------------+-----------------------------------------
2024-12-01 | {Inception,Interstellar,"The Prestige"}
2024-12-15 | {"The Matrix","John Wick"}
(2 rows)
Any element can be enclosed in double quotes and this is required, if the value contains commas or curly braces:
SELECT '{"{\"key1\": 1, \"key2\": \"value\"}", NULL, true}'::json[];
?column?
-----------------------------------------------
{"{\"key1\":1,\"key2\":\"value\"}",NULL,true}
(1 row)
In the example above, the double quotes which are a part of the JSON value are required to be escaped with a backslash, so that they are not mistaken with the double quote, which marks the end of the element
Accessing Arrays
You can retrieve a single element from an array using the array subscript operator. When it comes to array values indexing, the elements of an n-length array start at index 1
and end at index n
:
SELECT movies_planned,
movies_planned[1] AS first_movie,
movies_planned[3] AS third_movie
FROM movie_night;
movies_planned | first_movie | third_movie
-----------------------------------------+-------------+--------------
{Inception,Interstellar,"The Prestige"} | Inception | The Prestige
{"The Matrix","John Wick"} | The Matrix |
(2 rows)
If the index exceeds the length of an array, the returned value will be NULL
Arrays can also be accessed by using array slices. An array slice is denoted by writing lower_bound:upper_bound
. The bounds can be omitted, in which case the slice is unbounded from a given side:
SELECT movies_planned[:] as "unbounded slice",
movies_planned[1:2] AS "[1:2] slice",
movies_planned[2:] AS "[2:] slice"
FROM movie_night;
unbounded slice | [1:2] slice | [2:] slice
-----------------------------------------+----------------------------+-------------------------------
{Inception,Interstellar,"The Prestige"} | {Inception,Interstellar} | {Interstellar,"The Prestige"}
{"The Matrix","John Wick"} | {"The Matrix","John Wick"} | {"John Wick"}
(2 rows)
Limitations
Field Size Limit
In Oxla, the field size limit for variable-size types is 32MB and this limit applies to arrays as well. If a value exceeds the given limit, an error is returned:
CREATE TABLE tb (array_column bigint[]);
CREATE
COPY tb FROM '/.oxla/long_array_value.csv';
ERROR: Error in row 1, column array_column value exceeds size of 33554432
Unsupported SQL Clauses
Array columns cannot be used as the key columns in ORDER BY
, GROUP BY
or JOIN
operations. It is also impossible to use the array columns as a part of the index of a table. For all the operations mentioned above, an appropriate error message will be returned:
SELECT * FROM movie_night ORDER BY movies_planned;
ERROR: could not identify an ordering operator for type text[]
Arrays can still be used in ORDER BY
or JOIN
operations, if the array column is not the key:
SELECT * FROM movie_night ORDER BY event_date ASC;
event_date | movies_planned
------------+-----------------------------------------
2024-12-01 | {Inception,Interstellar,"The Prestige"}
2024-12-15 | {"The Matrix","John Wick"}
(2 rows)
Unsupported SQL Statements
Specific SQL statements currently do not support arrays. These include:
INSERT INTO
with SELECT
: Arrays cannot be directly imported using an INSERT INTO
with a SELECT
statement. Instead, we encourage you to either use the COPY FROM CSV
command or the INSERT INTO
statement with the VALUES
keyword
UPDATE
and DELETE
: Updating or deleting records from a table, which contains array columns is not supported
COPY TO
: Exporting data from array columns using the COPY TO
command is not available
CREATE INDEX
: Index on a table cannot be created on an array column.
Any effort to use such operations with arrays will result in an error. For now, these limitations should be considered when designing tables that include array columns.