---
title: "LIST and ARRAY type"
description: "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."
canonical: "https://motherduck.com/glossary/list-and-array-type/"
related:
  - title: "Data types | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/data-types/"
  - title: "DuckDB vs Pandas vs Polars for Python Developers"
    url: "https://motherduck.com/blog/duckdb-versus-pandas-versus-polars/"
  - title: "list_databases | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/mcp/list-databases/"
gated_asset:
  title: "DuckLake on MotherDuck"
  url: "https://motherduck.com/product/ducklake/"
---

# 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.

```sql
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.

```sql
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.