Array Type

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.

The ARRAY type in PostgreSQL allows variable-length fields. DuckDB's ARRAY type is fixed-length.

Creating Arrays

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

Construct with the array_value function:

SELECT array_value(1, 2, 3);

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

SELECT array_value(1, 2, 3)[2];

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

SELECT [3, 2, 1]::INTEGER[3];

Arrays can be nested:

SELECT array_value(array_value(1, 2), array_value(3, 4), array_value(5, 6));

Arrays can store structs:

SELECT array_value({'a': 1, 'b': 2}, {'a': 3, 'b': 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:

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:

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;

id	element
10	1
20	4

Using the slicing notation returns a LIST:

SELECT id, arr[1:2] AS elements FROM array_table;

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:

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:

SELECT array_cross_product(x.v, y.v)
FROM x, y
WHERE x.i = y.i;

Compute cosine similarity:

SELECT array_cosine_similarity(x.v, y.v)
FROM x, y
WHERE x.i = y.i;

Ordering