Skip to main content

Edit this page

Array

An ARRAY column stores fixed-sized arrays. All fields in the column must have the same length and the same underlying type. Arrays are typically used to store arrays of numbers, but can contain any uniform data type, including ARRAY, LIST and STRUCT types.

Arrays can be used to store vectors such as word embeddings or image embeddings.

To store variable-length lists, use the LIST type. See the data types overview for a comparison between nested data types.

Creating Arrays​

Arrays can be created using the array_value(expr, ...) function.

Construct with the array_value function:

Query
SELECT array_value(1, 2, 3);
Result
 array_value------------- {1,2,3}

You can always implicitly cast an array to a list (and use list functions, like list_extract, [i]):

Query
SELECT array_value(1, 2, 3)[2];
Result
 array_value-------------           2

You can cast from a list to an array (the dimensions have to match):

Query
SELECT [3, 2, 1]::INTEGER[3];
Result
 list_value------------ {3,2,1}

Arrays can be nested:

Query
SELECT array_value(array_value(1, 2), array_value(3, 4), array_value(5, 6));
Result
 array_value--------------------- {{1,2},{3,4},{5,6}}

Arrays can store structs:

Query
SELECT array_value({'a': 1, 'b': 2}, {'a': 3, 'b': 4});
Result
 array_value------------------- {"(1,2)","(3,4)"}

Defining an Array Field​

Arrays can be created using the ⟨TYPE_NAME⟩[⟨LENGTH⟩] syntax. For example, to create an array field for 3 integers, run:

Query
CREATE TABLE array_table (id INTEGER, arr INTEGER[3]);
INSERT INTO array_table VALUES (10, [1, 2, 3]), (20, [4, 5, 6]);

Retrieving Values from Arrays​

Retrieving one or more values from an array can be accomplished using brackets and slicing notation, or through list functions like list_extract and array_extract. Using the example in Defining an Array Field.

The following queries for extracting the first element of an array are equivalent:

Query
SELECT id, arr[1] AS element FROM array_table;
SELECT id, list_extract(arr, 1) AS element FROM array_table;
SELECT id, array_extract(arr, 1) AS element FROM array_table;
Result
 id | element----+--------- 10 |       1 20 |       4
 id | element----+--------- 10 |       1 20 |       4
 id | element----+--------- 10 |       1 20 |       4

Using the slicing notation returns a LIST:

Query
SELECT id, arr[1:2] AS elements FROM array_table;
Result
 id | elements----+---------- 10 | {1,2} 20 | {4,5}

Functions​

All LIST functions work with the ARRAY type. Additionally, several ARRAY-native functions are also supported. See the ARRAY functions.

Examples​

Create sample data:

Query
CREATE TABLE x (i INTEGER, v FLOAT[3]);
CREATE TABLE y (i INTEGER, v FLOAT[3]);
INSERT INTO x VALUES (1, array_value(1.0::FLOAT, 2.0::FLOAT, 3.0::FLOAT));
INSERT INTO y VALUES (1, array_value(2.0::FLOAT, 3.0::FLOAT, 4.0::FLOAT));

Compute cross product:

Query
SELECT array_cross_product(x.v, y.v)FROM x, yWHERE x.i = y.i;
Result
 array_cross_product--------------------- {-1,2,-1}

Compute cosine similarity:

Query
SELECT array_cosine_similarity(x.v, y.v)FROM x, yWHERE x.i = y.i;
Result
 array_cosine_similarity-------------------------               0.9925834

Ordering​

The ordering of ARRAY instances is defined using a lexicographical order. NULL values compare greater than all other values and are considered equal to each other.

See Also​

For more functions, see List Functions.