sqlalchemy/sqlalchemy · error · NotImplementedError

ARRAY.contains() not implemented for the base ARRAY type; pl

Error message

ARRAY.contains() not implemented for the base ARRAY type; please use the dialect-specific ARRAY type

What it means

Raised by the base ARRAY.Comparator.contains() method (sqltypes.py:3213) because the generic ARRAY type does not define a portable 'contains' operator. Only dialect-specific ARRAY types (notably sqlalchemy.dialects.postgresql.ARRAY) implement array containment (@>). The base class deliberately raises NotImplementedError to steer you to the dialect type.

Source

Thrown at lib/sqlalchemy/sql/sqltypes.py:3213

                if arr_type.dimensions is None or arr_type.dimensions == 1:
                    return_type = arr_type.item_type
                else:
                    adapt_kw = {"dimensions": arr_type.dimensions - 1}
                    return_type = arr_type.adapt(
                        arr_type.__class__, **adapt_kw
                    )

                return operators.getitem, index, return_type

        def contains(self, *arg: Any, **kw: Any) -> ColumnElement[bool]:
            """``ARRAY.contains()`` not implemented for the base ARRAY type.
            Use the dialect-specific ARRAY type.

            .. seealso::

                :class:`_postgresql.ARRAY` - PostgreSQL specific version.
            """
            raise NotImplementedError(
                "ARRAY.contains() not implemented for the base "
                "ARRAY type; please use the dialect-specific ARRAY type"
            )

        @util.deprecated(
            "2.1",
            message="The :meth:`_types.ARRAY.Comparator.any` and "
            ":meth:`_types.ARRAY.Comparator.all` methods for arrays are "
            "deprecated for removal, along with the PG-specific "
            ":func:`_postgresql.Any` and "
            ":func:`_postgresql.All` functions. See :func:`_sql.any_` and "
            ":func:`_sql.all_` functions for modern use.",
        )
        @util.preload_module("sqlalchemy.sql.elements")
        def any(
            self, other: Any, operator: Optional[OperatorType] = None
        ) -> ColumnElement[bool]:
            """Return ``other operator ANY (array)`` clause.

View on GitHub (pinned to d9b44cb731)

Solutions

Change the column type to sqlalchemy.dialects.postgresql.ARRAY, whose Comparator implements contains() via the @> operator.
Rewrite the predicate using a portable expression such as .any()/.all_() (note any/all are deprecated in 2.1 in favor of sqlalchemy.sql.any_/all_) or a raw text/func construct.
If multi-database support is required, branch the query construction per dialect instead of relying on the base ARRAY.

Example fix

// before
from sqlalchemy import Column, ARRAY, Integer
amounts = Column(ARRAY(Integer))
stmt = select(T).where(T.amounts.contains([5]))

// after
from sqlalchemy.dialects.postgresql import ARRAY as PG_ARRAY
amounts = Column(PG_ARRAY(Integer))
stmt = select(T).where(T.amounts.contains([5]))

Defensive patterns

Strategy: type-guard

Validate before calling

from sqlalchemy.sql.sqltypes import ARRAY as GenericARRAY

def assert_dialect_array_supports_contains(col_type):
    # Generic ARRAY does not implement .contains(); only dialect arrays do.
    if type(col_type) is GenericARRAY:
        raise TypeError(
            "Use a dialect-specific ARRAY (e.g. sqlalchemy.dialects.postgresql.ARRAY) "
            "to call .contains()."
        )
    return col_type

Type guard

from sqlalchemy.sql.sqltypes import ARRAY as GenericARRAY

def supports_contains(col_type) -> bool:
    # Generic base ARRAY returns True only for dialect subclasses.
    return not type(col_type) is GenericARRAY

Try / catch

try:
    stmt = select(MyTable).where(MyTable.tags.contains(["x"]))
except NotImplementedError as err:
    if "contains() not implemented" in str(err):
        # fall back to a portable operator, e.g. overlap (&&) or any():
        stmt = select(MyTable).where(MyTable.tags.any(lambda t: t == "x"))
    else:
        raise

Prevention

Import ARRAY from the target dialect (`from sqlalchemy.dialects.postgresql import ARRAY`) when you need `.contains()` or other array operators.
Prefer portable expressions like `.any()`/`.all()` or raw operators (`&&`, `@>`) over `.contains()` if you may run on multiple dialects.
In a model registry, record which columns are dialect-specific so generic ARRAYs are never queried with contains().

When it happens

Trigger: Calling MyTable.col.contains([...]) where col is typed with the generic sqlalchemy.types.ARRAY rather than the PostgreSQL-specific ARRAY; using the base ARRAY with a backend (or in-memory compilation) that does not provide a contains operator.

Common situations: Defining a cross-database model with generic ARRAY then attempting PostgreSQL-style containment queries; copy-paste of PG examples into a model that imported ARRAY from the top-level package; forgetting to switch the column type after deciding to target PostgreSQL.

Related errors

AI-assisted analysis of sqlalchemy/sqlalchemy@d9b44cb731 (2026-08-01). Data as JSON: /data/errors/0788a8708d69c651.json. Report an issue: GitHub.