sqlalchemy/sqlalchemy · error · TypeError
at least one ORDER BY element is required
Error message
at least one ORDER BY element is required
What it means
Raised (as TypeError) by the PostgreSQL dialect's aggregate_order_by constructor in ext.py. This helper builds the 'array_agg(x ORDER BY y)' form; the SQL requires at least one ORDER BY expression, so passing zero order_by positional args is invalid.
Source
Thrown at lib/sqlalchemy/dialects/postgresql/ext.py:112
self,
target: _ColumnExpressionArgument[_T],
*order_by: _ColumnExpressionArgument[Any],
): ...
def __init__(
self,
target: _ColumnExpressionArgument[_T],
*order_by: _ColumnExpressionArgument[Any],
):
self.target: ClauseElement = coercions.expect(
roles.ExpressionElementRole, target
)
self.type = self.target.type
_lob = len(order_by)
self.order_by: ClauseElement
if _lob == 0:
raise TypeError("at least one ORDER BY element is required")
elif _lob == 1:
self.order_by = coercions.expect(
roles.ExpressionElementRole, order_by[0]
)
else:
self.order_by = elements.ClauseList(
*order_by, _literal_as_text_role=roles.ExpressionElementRole
)
def self_group(
self, against: Optional[OperatorType] = None
) -> ClauseElement:
return self
def get_children(self, **kwargs: Any) -> Iterable[ClauseElement]:
return self.target, self.order_by
def _copy_internals(View on GitHub (pinned to d9b44cb731)
Solutions
- Add at least one ORDER BY expression: aggregate_order_by(func.array_agg(t.c.x), t.c.y.desc()).
- Prefer the Core-level dialect-agnostic func aggregate ordering API (Function.aggregate_order_by) instead of the legacy postgresql.ext helper.
Example fix
// before from sqlalchemy.dialects.postgresql import aggregate_order_by stmt = select(func.array_agg(aggregate_order_by(t.c.x))) // after from sqlalchemy.dialects.postgresql import aggregate_order_by stmt = select(func.array_agg(aggregate_order_by(t.c.x, t.c.y.desc())))
Defensive patterns
Strategy: validation
Validate before calling
from sqlalchemy.dialects.postgresql import aggregate_order_by
def safe_aggregate_order_by(target, *order_by):
if len(order_by) == 0:
raise TypeError("aggregate_order_by requires at least one ORDER BY element")
return aggregate_order_by(target, *order_by)
# e.g. array_agg(col ORDER BY a, b)
safe_aggregate_order_by(my_table.c.col, my_table.c.sort_col) Type guard
def has_order_by(*order_by) -> bool:
return len(order_by) >= 1 and all(o is not None for o in order_by) Prevention
- aggregate_order_by(target, *order_by) is positional; always include at least one order_by column.
- If order_by comes from user input, default it (e.g. to an id column) rather than splatting an empty list.
- Note this raises TypeError, not ValueError — assert in tests that the call has >=2 positional args.
When it happens
Trigger: Calling func.aggregate_order_by(target) or postgresql.ext.aggregate_order_by(target) with only the target column and no following ORDER BY expression. Note: a TypeError is raised here (not ValueError).
Common situations: Developers forget the trailing order-by column, dynamically construct the call with *args that expand to nothing, or migrate to the legacy postgresql aggregate_order_by without supplying the ordering column. Modern code should prefer the Core-level func.array_agg(...).aggregate_order_by(...) or just func.array_agg(col) with .within_group().
Related errors
- Do not nest ARRAY types; ARRAY(basetype) handles multi-dimen
- BitString must only contain '0' and '1' chars
- value must be non-negative
- length must be non-negative
- Cannot encode {value} as a BitString of length {length}
AI-assisted analysis of sqlalchemy/sqlalchemy@d9b44cb731 (2026-08-01).
Data as JSON: /data/errors/a640721ce5ddec59.json.
Report an issue: GitHub.