SQLAlchemy + Postgres Cheatsheet 03 — Arrays

Cheatsheet for Postgres native ARRAY columns.

Column

from sqlalchemy.dialects.postgresql import ARRAY
from sqlalchemy import String, Integer

class Post(Base):
    id: Mapped[int] = mapped_column(primary_key=True)
    tags: Mapped[list[str]] = mapped_column(ARRAY(String))
    scores: Mapped[list[int]] = mapped_column(ARRAY(Integer))
    matrix: Mapped[list[list[int]]] = mapped_column(ARRAY(Integer, dimensions=2))

Queries

from sqlalchemy import text, func

# Contains
stmt = select(Post).where(Post.tags.contains(["python", "fastapi"]))   # array @> array

# Overlaps (has any element in common)
stmt = select(Post).where(Post.tags.overlap(["python", "rust"]))

# Single-element ANY
stmt = select(Post).where(text("'python' = ANY(tags)"))

# Length
stmt = select(Post).where(func.array_length(Post.tags, 1) > 5)

# Specific index (1-based)
stmt = select(Post.tags[1])

Append / remove

# Append
await session.execute(
    update(Post).where(Post.id == 1).values(tags=Post.tags + ["new-tag"])
)

# Remove element by value (use array_remove)
await session.execute(
    update(Post).where(Post.id == 1).values(tags=func.array_remove(Post.tags, "old-tag"))
)

# Prepend
await session.execute(
    update(Post).where(Post.id == 1).values(tags=text("ARRAY['new-tag'] || tags"))
)

GIN index

__table_args__ = (
    Index("ix_posts_tags_gin", "tags", postgresql_using="gin"),
)

Efficient for containment / overlap queries.

Unnest (rows from array)

stmt = select(func.unnest(Post.tags).label("tag")).where(Post.id == 1)

Useful in subqueries or join lateral.

array_agg (aggregate to array)

stmt = (
    select(Post.author_id, func.array_agg(Post.tags))
    .group_by(Post.author_id)
)

Pass array as parameter

ids = [1, 2, 3]
stmt = select(User).where(User.id == func.any_(ids))

func.any_(array) for = ANY (array).

Bulk insert with array column

await session.execute(insert(Post), [
    {"tags": ["a", "b"], "title": "..."},
    {"tags": ["c"], "title": "..."},
])

Constraints on arrays

__table_args__ = (
    CheckConstraint("array_length(tags, 1) <= 20", name="ck_posts_max_tags"),
)

Trigram on array elements (combine with unnest + trigram)

For text-search within tags:

SELECT * FROM posts p
WHERE EXISTS (
    SELECT 1 FROM unnest(p.tags) t WHERE t ILIKE '%py%'
);

In SQLA:

from sqlalchemy import literal, exists, select as core_select

stmt = select(Post).where(
    exists(
        core_select(literal(1)).select_from(func.unnest(Post.tags).alias("t"))
        .where(text("t ILIKE :p"))
    ),
    {"p": "%py%"},
)

Or denormalize tags into a separate tags table for richer queries.

Cross-DB note

MySQL / SQLite don’t have native arrays. Use JSON column with a list, or normalize.

When NOT to use ARRAY

Need to query by individual elements with rich predicates (use a join table).
Many writes to the same row (write amplification of full rewrite).
Cross-DB portability.

Common mistakes

1-based vs 0-based indexing — Postgres arrays are 1-based.
array_length(arr, 1) returns NULL for empty arrays — check.
Mutating ARRAY by re-assigning whole array — sometimes you want array_append/array_remove.
Missing GIN — slow contains.

Column#

Queries#

Append / remove#

GIN index#

Unnest (rows from array)#

array_agg (aggregate to array)#

Pass array as parameter#

Bulk insert with array column#

Constraints on arrays#

Trigram on array elements (combine with unnest + trigram)#

Cross-DB note#

When NOT to use ARRAY#

Common mistakes#

Read this next#