Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1# dialects/postgresql/array.py
2# Copyright (C) 2005-2026 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: https://www.opensource.org/licenses/mit-license.php
9from __future__ import annotations
11import re
12from typing import Any as typing_Any
13from typing import Iterable
14from typing import Optional
15from typing import Sequence
16from typing import TYPE_CHECKING
17from typing import TypeVar
18from typing import Union
20from .operators import CONTAINED_BY
21from .operators import CONTAINS
22from .operators import OVERLAP
23from ... import types as sqltypes
24from ... import util
25from ...sql import expression
26from ...sql import operators
27from ...sql.visitors import InternalTraversal
29if TYPE_CHECKING:
30 from ...engine.interfaces import Dialect
31 from ...sql._typing import _ColumnExpressionArgument
32 from ...sql._typing import _TypeEngineArgument
33 from ...sql.elements import ColumnElement
34 from ...sql.elements import Grouping
35 from ...sql.expression import BindParameter
36 from ...sql.operators import OperatorType
37 from ...sql.selectable import _SelectIterable
38 from ...sql.type_api import _BindProcessorType
39 from ...sql.type_api import _LiteralProcessorType
40 from ...sql.type_api import _ResultProcessorType
41 from ...sql.type_api import TypeEngine
42 from ...sql.visitors import _TraverseInternalsType
43 from ...util.typing import Self
46_T = TypeVar("_T", bound=typing_Any)
47_CT = TypeVar("_CT", bound=typing_Any)
50def Any(
51 other: typing_Any,
52 arrexpr: _ColumnExpressionArgument[_T],
53 operator: OperatorType = operators.eq,
54) -> ColumnElement[bool]:
55 """A synonym for the ARRAY-level :meth:`.ARRAY.Comparator.any` method.
56 See that method for details.
58 .. deprecated:: 2.1
60 The :meth:`_types.ARRAY.Comparator.any` and
61 :meth:`_types.ARRAY.Comparator.all` methods for arrays are deprecated
62 for removal, along with the PG-specific :func:`_postgresql.Any` and
63 :func:`_postgresql.All` functions. See :func:`_sql.any_` and
64 :func:`_sql.all_` functions for modern use.
67 """
69 return arrexpr.any(other, operator) # type: ignore[no-any-return, union-attr] # noqa: E501
72def All(
73 other: typing_Any,
74 arrexpr: _ColumnExpressionArgument[_T],
75 operator: OperatorType = operators.eq,
76) -> ColumnElement[bool]:
77 """A synonym for the ARRAY-level :meth:`.ARRAY.Comparator.all` method.
78 See that method for details.
80 .. deprecated:: 2.1
82 The :meth:`_types.ARRAY.Comparator.any` and
83 :meth:`_types.ARRAY.Comparator.all` methods for arrays are deprecated
84 for removal, along with the PG-specific :func:`_postgresql.Any` and
85 :func:`_postgresql.All` functions. See :func:`_sql.any_` and
86 :func:`_sql.all_` functions for modern use.
88 """
90 return arrexpr.all(other, operator) # type: ignore[no-any-return, union-attr] # noqa: E501
93class array(expression.ExpressionClauseList[_T]):
94 """A PostgreSQL ARRAY literal.
96 This is used to produce ARRAY literals in SQL expressions, e.g.::
98 from sqlalchemy.dialects.postgresql import array
99 from sqlalchemy.dialects import postgresql
100 from sqlalchemy import select, func
102 stmt = select(array([1, 2]) + array([3, 4, 5]))
104 print(stmt.compile(dialect=postgresql.dialect()))
106 Produces the SQL:
108 .. sourcecode:: sql
110 SELECT ARRAY[%(param_1)s, %(param_2)s] ||
111 ARRAY[%(param_3)s, %(param_4)s, %(param_5)s]) AS anon_1
113 An instance of :class:`.array` will always have the datatype
114 :class:`_types.ARRAY`. The "inner" type of the array is inferred from the
115 values present, unless the :paramref:`_postgresql.array.type_` keyword
116 argument is passed::
118 array(["foo", "bar"], type_=CHAR)
120 When constructing an empty array, the :paramref:`_postgresql.array.type_`
121 argument is particularly important as PostgreSQL server typically requires
122 a cast to be rendered for the inner type in order to render an empty array.
123 SQLAlchemy's compilation for the empty array will produce this cast so
124 that::
126 stmt = array([], type_=Integer)
127 print(stmt.compile(dialect=postgresql.dialect()))
129 Produces:
131 .. sourcecode:: sql
133 ARRAY[]::INTEGER[]
135 As required by PostgreSQL for empty arrays.
137 .. versionadded:: 2.0.40 added support to render empty PostgreSQL array
138 literals with a required cast.
140 Multidimensional arrays are produced by nesting :class:`.array` constructs.
141 The dimensionality of the final :class:`_types.ARRAY`
142 type is calculated by
143 recursively adding the dimensions of the inner :class:`_types.ARRAY`
144 type::
146 stmt = select(
147 array(
148 [array([1, 2]), array([3, 4]), array([column("q"), column("x")])]
149 )
150 )
151 print(stmt.compile(dialect=postgresql.dialect()))
153 Produces:
155 .. sourcecode:: sql
157 SELECT ARRAY[
158 ARRAY[%(param_1)s, %(param_2)s],
159 ARRAY[%(param_3)s, %(param_4)s],
160 ARRAY[q, x]
161 ] AS anon_1
163 .. seealso::
165 :class:`_postgresql.ARRAY`
167 """ # noqa: E501
169 __visit_name__ = "array"
171 stringify_dialect = "postgresql"
173 _traverse_internals: _TraverseInternalsType = [
174 ("clauses", InternalTraversal.dp_clauseelement_tuple),
175 ("type", InternalTraversal.dp_type),
176 ]
178 def __init__(
179 self,
180 clauses: Iterable[_T],
181 *,
182 type_: Optional[_TypeEngineArgument[_T]] = None,
183 **kw: typing_Any,
184 ):
185 r"""Construct an ARRAY literal.
187 :param clauses: iterable, such as a list, containing elements to be
188 rendered in the array
189 :param type\_: optional type. If omitted, the type is inferred
190 from the contents of the array.
192 """
193 super().__init__(operators.comma_op, *clauses, **kw)
195 main_type = (
196 type_
197 if type_ is not None
198 else self.clauses[0].type if self.clauses else sqltypes.NULLTYPE
199 )
201 if isinstance(main_type, ARRAY):
202 self.type = ARRAY(
203 main_type.item_type,
204 dimensions=(
205 main_type.dimensions + 1
206 if main_type.dimensions is not None
207 else 2
208 ),
209 ) # type: ignore[assignment]
210 else:
211 self.type = ARRAY(main_type) # type: ignore[assignment]
213 @property
214 def _select_iterable(self) -> _SelectIterable:
215 return (self,)
217 def _bind_param(
218 self,
219 operator: OperatorType,
220 obj: typing_Any,
221 type_: Optional[TypeEngine[_T]] = None,
222 _assume_scalar: bool = False,
223 ) -> BindParameter[_T]:
224 if _assume_scalar or operator is operators.getitem:
225 return expression.BindParameter(
226 None,
227 obj,
228 _compared_to_operator=operator,
229 type_=type_,
230 _compared_to_type=self.type,
231 unique=True,
232 )
234 else:
235 return array(
236 [
237 self._bind_param(
238 operator, o, _assume_scalar=True, type_=type_
239 )
240 for o in obj
241 ]
242 ) # type: ignore[return-value]
244 def self_group(
245 self, against: Optional[OperatorType] = None
246 ) -> Union[Self, Grouping[_T]]:
247 if against in (operators.any_op, operators.all_op, operators.getitem):
248 return expression.Grouping(self)
249 else:
250 return self
253class ARRAY(sqltypes.ARRAY[_T]):
254 """PostgreSQL ARRAY type.
256 The :class:`_postgresql.ARRAY` type is constructed in the same way
257 as the core :class:`_types.ARRAY` type; a member type is required, and a
258 number of dimensions is recommended if the type is to be used for more
259 than one dimension::
261 from sqlalchemy.dialects import postgresql
263 mytable = Table(
264 "mytable",
265 metadata,
266 Column("data", postgresql.ARRAY(Integer, dimensions=2)),
267 )
269 The :class:`_postgresql.ARRAY` type provides all operations defined on the
270 core :class:`_types.ARRAY` type, including support for "dimensions",
271 indexed access, and simple matching such as
272 :meth:`.types.ARRAY.Comparator.any` and
273 :meth:`.types.ARRAY.Comparator.all`. :class:`_postgresql.ARRAY`
274 class also
275 provides PostgreSQL-specific methods for containment operations, including
276 :meth:`.postgresql.ARRAY.Comparator.contains`
277 :meth:`.postgresql.ARRAY.Comparator.contained_by`, and
278 :meth:`.postgresql.ARRAY.Comparator.overlap`, e.g.::
280 mytable.c.data.contains([1, 2])
282 Indexed access is one-based by default, to match that of PostgreSQL;
283 for zero-based indexed access, set
284 :paramref:`_postgresql.ARRAY.zero_indexes`.
286 Additionally, the :class:`_postgresql.ARRAY`
287 type does not work directly in
288 conjunction with the :class:`.ENUM` type. For a workaround, see the
289 special type at :ref:`postgresql_array_of_enum`.
291 .. container:: topic
293 **Detecting Changes in ARRAY columns when using the ORM**
295 The :class:`_postgresql.ARRAY` type, when used with the SQLAlchemy ORM,
296 does not detect in-place mutations to the array. In order to detect
297 these, the :mod:`sqlalchemy.ext.mutable` extension must be used, using
298 the :class:`.MutableList` class::
300 from sqlalchemy.dialects.postgresql import ARRAY
301 from sqlalchemy.ext.mutable import MutableList
304 class SomeOrmClass(Base):
305 # ...
307 data = Column(MutableList.as_mutable(ARRAY(Integer)))
309 This extension will allow "in-place" changes such to the array
310 such as ``.append()`` to produce events which will be detected by the
311 unit of work. Note that changes to elements **inside** the array,
312 including subarrays that are mutated in place, are **not** detected.
314 Alternatively, assigning a new array value to an ORM element that
315 replaces the old one will always trigger a change event.
317 .. seealso::
319 :class:`_types.ARRAY` - base array type
321 :class:`_postgresql.array` - produces a literal array value.
323 """
325 def __init__(
326 self,
327 item_type: _TypeEngineArgument[_T],
328 as_tuple: bool = False,
329 dimensions: Optional[int] = None,
330 zero_indexes: bool = False,
331 ):
332 """Construct an ARRAY.
334 E.g.::
336 Column("myarray", ARRAY(Integer))
338 Arguments are:
340 :param item_type: The data type of items of this array. Note that
341 dimensionality is irrelevant here, so multi-dimensional arrays like
342 ``INTEGER[][]``, are constructed as ``ARRAY(Integer)``, not as
343 ``ARRAY(ARRAY(Integer))`` or such.
345 :param as_tuple=False: Specify whether return results
346 should be converted to tuples from lists. DBAPIs such
347 as psycopg2 return lists by default. When tuples are
348 returned, the results are hashable.
350 :param dimensions: if non-None, the ARRAY will assume a fixed
351 number of dimensions. This will cause the DDL emitted for this
352 ARRAY to include the exact number of bracket clauses ``[]``,
353 and will also optimize the performance of the type overall.
354 Note that PG arrays are always implicitly "non-dimensioned",
355 meaning they can store any number of dimensions no matter how
356 they were declared.
358 :param zero_indexes=False: when True, index values will be converted
359 between Python zero-based and PostgreSQL one-based indexes, e.g.
360 a value of one will be added to all index values before passing
361 to the database.
363 """
364 if isinstance(item_type, ARRAY):
365 raise ValueError(
366 "Do not nest ARRAY types; ARRAY(basetype) "
367 "handles multi-dimensional arrays of basetype"
368 )
369 if isinstance(item_type, type):
370 item_type = item_type()
371 self.item_type = item_type
372 self.as_tuple = as_tuple
373 self.dimensions = dimensions
374 self.zero_indexes = zero_indexes
376 class Comparator(sqltypes.ARRAY.Comparator[_CT]):
377 """Define comparison operations for :class:`_types.ARRAY`.
379 Note that these operations are in addition to those provided
380 by the base :class:`.types.ARRAY.Comparator` class, including
381 :meth:`.types.ARRAY.Comparator.any` and
382 :meth:`.types.ARRAY.Comparator.all`.
384 """
386 def contains(
387 self, other: typing_Any, **kwargs: typing_Any
388 ) -> ColumnElement[bool]:
389 """Boolean expression. Test if elements are a superset of the
390 elements of the argument array expression.
392 kwargs may be ignored by this operator but are required for API
393 conformance.
394 """
395 return self.operate(CONTAINS, other, result_type=sqltypes.Boolean)
397 def contained_by(self, other: typing_Any) -> ColumnElement[bool]:
398 """Boolean expression. Test if elements are a proper subset of the
399 elements of the argument array expression.
400 """
401 return self.operate(
402 CONTAINED_BY, other, result_type=sqltypes.Boolean
403 )
405 def overlap(self, other: typing_Any) -> ColumnElement[bool]:
406 """Boolean expression. Test if array has elements in common with
407 an argument array expression.
408 """
409 return self.operate(OVERLAP, other, result_type=sqltypes.Boolean)
411 comparator_factory = Comparator
413 @util.memoized_property
414 def _against_native_enum(self) -> bool:
415 return (
416 isinstance(self.item_type, sqltypes.Enum)
417 and self.item_type.native_enum
418 )
420 def literal_processor(
421 self, dialect: Dialect
422 ) -> Optional[_LiteralProcessorType[_T]]:
423 item_proc = self.item_type.dialect_impl(dialect).literal_processor(
424 dialect
425 )
426 if item_proc is None:
427 return None
429 def to_str(elements: Iterable[typing_Any]) -> str:
430 return f"ARRAY[{', '.join(elements)}]"
432 def process(value: Sequence[typing_Any]) -> str:
433 inner = self._apply_item_processor(
434 value, item_proc, self.dimensions, to_str
435 )
436 return inner
438 return process
440 def bind_processor(
441 self, dialect: Dialect
442 ) -> Optional[_BindProcessorType[Sequence[typing_Any]]]:
443 item_proc = self.item_type.dialect_impl(dialect).bind_processor(
444 dialect
445 )
447 def process(
448 value: Optional[Sequence[typing_Any]],
449 ) -> Optional[list[typing_Any]]:
450 if value is None:
451 return value
452 else:
453 return self._apply_item_processor(
454 value, item_proc, self.dimensions, list
455 )
457 return process
459 def result_processor(
460 self, dialect: Dialect, coltype: object
461 ) -> _ResultProcessorType[Sequence[typing_Any]]:
462 item_proc = self.item_type.dialect_impl(dialect).result_processor(
463 dialect, coltype
464 )
466 def process(
467 value: Sequence[typing_Any],
468 ) -> Optional[Sequence[typing_Any]]:
469 if value is None:
470 return value
471 else:
472 return self._apply_item_processor(
473 value,
474 item_proc,
475 self.dimensions,
476 tuple if self.as_tuple else list,
477 )
479 if self._against_native_enum:
480 super_rp = process
481 pattern = re.compile(r"^{(.*)}$")
483 def handle_raw_string(value: str) -> Sequence[Optional[str]]:
484 inner = pattern.match(value).group(1) # type: ignore[union-attr] # noqa: E501
485 return _split_enum_values(inner)
487 def process(
488 value: Sequence[typing_Any],
489 ) -> Optional[Sequence[typing_Any]]:
490 if value is None:
491 return value
492 # isinstance(value, str) is required to handle
493 # the case where a TypeDecorator for and Array of Enum is
494 # used like was required in sa < 1.3.17
495 return super_rp(
496 handle_raw_string(value)
497 if isinstance(value, str)
498 else value
499 )
501 return process
504def _split_enum_values(array_string: str) -> Sequence[Optional[str]]:
505 if '"' not in array_string:
506 # no escape char is present so it can just split on the comma
507 return [
508 r if r != "NULL" else None
509 for r in (array_string.split(",") if array_string else [])
510 ]
512 # handles quoted strings from:
513 # r'abc,"quoted","also\\\\quoted", "quoted, comma", "esc \" quot", qpr'
514 # returns
515 # ['abc', 'quoted', 'also\\quoted', 'quoted, comma', 'esc " quot', 'qpr']
516 text = array_string.replace(r"\"", "_$ESC_QUOTE$_")
517 text = text.replace(r"\\", "\\")
518 result = []
519 on_quotes = re.split(r'(")', text)
520 in_quotes = False
521 for tok in on_quotes:
522 if tok == '"':
523 in_quotes = not in_quotes
524 elif in_quotes:
525 result.append(tok.replace("_$ESC_QUOTE$_", '"'))
526 else:
527 # interpret NULL (without quotes!) as None
528 result.extend(
529 [
530 r if r != "NULL" else None
531 for r in re.findall(r"([^\s,]+),?", tok)
532 ]
533 )
534 return result