LIST and ARRAY type

LIST and ARRAY are nested SQL data types for storing an ordered collection of values in a single column — LIST is variable-length, ARRAY is fixed-length.

Overview

Many modern SQL engines support nested, ordered collection types alongside scalar column types like INTEGER or VARCHAR. These let a single column hold multiple values (e.g., a set of tags, embedding vector, or time series) without a separate join table.

Copy code
CREATE TABLE customers (
  id INTEGER,
  tags VARCHAR[]           -- a list of strings
);

INSERT INTO customers VALUES (1, ['vip', 'newsletter']);

SELECT id FROM customers WHERE list_contains(tags, 'vip');

LIST vs ARRAY

The terms are used inconsistently across SQL engines. In DuckDB specifically:

LIST (type[], e.g. INTEGER[]) is a variable-length ordered collection — different rows can have lists of different lengths.
ARRAY (type[N], e.g. INTEGER[3]) is a fixed-length ordered collection — every value in the column must have exactly N elements.

This is a deliberate departure from Postgres, where ARRAY is variable-length; DuckDB's fixed-size ARRAY is optimized for dense numeric data like embedding vectors, where a constant width enables more compact, vectorized storage and computation.

Copy code
CREATE TABLE embeddings (id INTEGER, vec FLOAT[384]);   -- fixed-size ARRAY
CREATE TABLE orders (id INTEGER, line_items VARCHAR[]); -- variable-length LIST

DuckDB specifics

DuckDB provides a rich function library for both types: list_value(...)/bracket literals ([1, 2, 3]) to construct, list_contains, list_transform, list_filter, list_aggregate, len(), and UNNEST to flatten. ARRAY supports element-wise operations useful for vector similarity search, such as array_distance and array_cosine_similarity, which is why fixed-size arrays are commonly used for embedding columns in DuckDB-backed vector search workflows.

Related terms

UNNEST →

UNNEST expands a list (array) or struct column into multiple rows or columns, turning nested data into a flat, relational shape.

Vector database →

A vector database stores high-dimensional numeric vectors (typically embeddings) and provides fast similarity search over them, powering use cases like semantic search and retrieval-augmented generation.

MAP type →

MAP is a nested SQL data type storing an ordered collection of unique key-value pairs, useful for representing dynamic or sparse attributes within a single column.

Embeddings →

Embeddings are dense numeric vector representations of data — text, images, audio, or other objects — learned so that semantically similar inputs end up close together in vector space.

NumPy →

NumPy is the core Python library for numerical computing, providing a fast, memory-efficient N-dimensional array type and vectorized math operations that most of the Python data-science stack is built on.

aggregate functions →

Aggregate functions are SQL operations that perform calculations across multiple rows of data to produce a single summary value.

FAQS

Use ARRAY (fixed-size, e.g. FLOAT[384]) when every row must hold exactly the same number of elements, such as embedding vectors — this enables more compact storage and vectorized operations like array_cosine_similarity. Use LIST (variable-length) for collections like tags or line items that differ in size per row.

No. PostgreSQL's ARRAY type is variable-length, closer to what DuckDB calls LIST. DuckDB's ARRAY type is specifically fixed-length, where every value in the column must have the same declared size.