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 _CovariantTypeEngineArgument
33 from ...sql._typing import _TypeEngineArgument
34 from ...sql.elements import ColumnElement
35 from ...sql.elements import Grouping
36 from ...sql.expression import BindParameter
37 from ...sql.operators import OperatorType
38 from ...sql.selectable import _SelectIterable
39 from ...sql.type_api import _BindProcessorType
40 from ...sql.type_api import _LiteralProcessorType
41 from ...sql.type_api import _ResultProcessorType
42 from ...sql.type_api import TypeEngine
43 from ...sql.visitors import _TraverseInternalsType
44 from ...util.typing import Self
47_T = TypeVar("_T", bound=typing_Any)
48_CT = TypeVar("_CT", bound=typing_Any)
51def Any(
52 other: typing_Any,
53 arrexpr: _ColumnExpressionArgument[_T],
54 operator: OperatorType = operators.eq,
55) -> ColumnElement[bool]:
56 """A synonym for the ARRAY-level :meth:`.ARRAY.Comparator.any` method.
57 See that method for details.
59 .. deprecated:: 2.1
61 The :meth:`_types.ARRAY.Comparator.any` and
62 :meth:`_types.ARRAY.Comparator.all` methods for arrays are deprecated
63 for removal, along with the PG-specific :func:`_postgresql.Any` and
64 :func:`_postgresql.All` functions. See :func:`_sql.any_` and
65 :func:`_sql.all_` functions for modern use.
68 """
70 return arrexpr.any(other, operator) # type: ignore[no-any-return, union-attr] # noqa: E501
73def All(
74 other: typing_Any,
75 arrexpr: _ColumnExpressionArgument[_T],
76 operator: OperatorType = operators.eq,
77) -> ColumnElement[bool]:
78 """A synonym for the ARRAY-level :meth:`.ARRAY.Comparator.all` method.
79 See that method for details.
81 .. deprecated:: 2.1
83 The :meth:`_types.ARRAY.Comparator.any` and
84 :meth:`_types.ARRAY.Comparator.all` methods for arrays are deprecated
85 for removal, along with the PG-specific :func:`_postgresql.Any` and
86 :func:`_postgresql.All` functions. See :func:`_sql.any_` and
87 :func:`_sql.all_` functions for modern use.
89 """
91 return arrexpr.all(other, operator) # type: ignore[no-any-return, union-attr] # noqa: E501
94class array(expression.ExpressionClauseList[_T]):
95 """A PostgreSQL ARRAY literal.
97 This is used to produce ARRAY literals in SQL expressions, e.g.::
99 from sqlalchemy.dialects.postgresql import array
100 from sqlalchemy.dialects import postgresql
101 from sqlalchemy import select, func
103 stmt = select(array([1, 2]) + array([3, 4, 5]))
105 print(stmt.compile(dialect=postgresql.dialect()))
107 Produces the SQL:
109 .. sourcecode:: sql
111 SELECT ARRAY[%(param_1)s, %(param_2)s] ||
112 ARRAY[%(param_3)s, %(param_4)s, %(param_5)s]) AS anon_1
114 An instance of :class:`.array` will always have the datatype
115 :class:`_types.ARRAY`. The "inner" type of the array is inferred from the
116 values present, unless the :paramref:`_postgresql.array.type_` keyword
117 argument is passed::
119 array(["foo", "bar"], type_=CHAR)
121 When constructing an empty array, the :paramref:`_postgresql.array.type_`
122 argument is particularly important as PostgreSQL server typically requires
123 a cast to be rendered for the inner type in order to render an empty array.
124 SQLAlchemy's compilation for the empty array will produce this cast so
125 that::
127 stmt = array([], type_=Integer)
128 print(stmt.compile(dialect=postgresql.dialect()))
130 Produces:
132 .. sourcecode:: sql
134 ARRAY[]::INTEGER[]
136 As required by PostgreSQL for empty arrays.
138 .. versionadded:: 2.0.40 added support to render empty PostgreSQL array
139 literals with a required cast.
141 Multidimensional arrays are produced by nesting :class:`.array` constructs.
142 The dimensionality of the final :class:`_types.ARRAY`
143 type is calculated by
144 recursively adding the dimensions of the inner :class:`_types.ARRAY`
145 type::
147 stmt = select(
148 array(
149 [array([1, 2]), array([3, 4]), array([column("q"), column("x")])]
150 )
151 )
152 print(stmt.compile(dialect=postgresql.dialect()))
154 Produces:
156 .. sourcecode:: sql
158 SELECT ARRAY[
159 ARRAY[%(param_1)s, %(param_2)s],
160 ARRAY[%(param_3)s, %(param_4)s],
161 ARRAY[q, x]
162 ] AS anon_1
164 .. seealso::
166 :class:`_postgresql.ARRAY`
168 """ # noqa: E501
170 __visit_name__ = "array"
172 stringify_dialect = "postgresql"
174 _traverse_internals: _TraverseInternalsType = [
175 ("clauses", InternalTraversal.dp_clauseelement_tuple),
176 ("type", InternalTraversal.dp_type),
177 ]
179 def __init__(
180 self,
181 clauses: Iterable[_T],
182 *,
183 type_: Optional[_TypeEngineArgument[_T]] = None,
184 **kw: typing_Any,
185 ):
186 r"""Construct an ARRAY literal.
188 :param clauses: iterable, such as a list, containing elements to be
189 rendered in the array
190 :param type\_: optional type. If omitted, the type is inferred
191 from the contents of the array.
193 """
194 super().__init__(operators.comma_op, *clauses, **kw)
196 main_type = (
197 type_
198 if type_ is not None
199 else self.clauses[0].type if self.clauses else sqltypes.NULLTYPE
200 )
202 if isinstance(main_type, ARRAY):
203 self.type = ARRAY(
204 main_type.item_type,
205 dimensions=(
206 main_type.dimensions + 1
207 if main_type.dimensions is not None
208 else 2
209 ),
210 ) # type: ignore[assignment]
211 else:
212 self.type = ARRAY(main_type) # type: ignore[assignment]
214 @property
215 def _select_iterable(self) -> _SelectIterable:
216 return (self,)
218 def _bind_param(
219 self,
220 operator: OperatorType,
221 obj: typing_Any,
222 type_: Optional[TypeEngine[_T]] = None,
223 _assume_scalar: bool = False,
224 ) -> BindParameter[_T]:
225 if _assume_scalar or operator is operators.getitem:
226 return expression.BindParameter(
227 None,
228 obj,
229 _compared_to_operator=operator,
230 type_=type_,
231 _compared_to_type=self.type,
232 unique=True,
233 )
235 else:
236 return array(
237 [
238 self._bind_param(
239 operator, o, _assume_scalar=True, type_=type_
240 )
241 for o in obj
242 ]
243 ) # type: ignore[return-value]
245 def self_group(
246 self, against: Optional[OperatorType] = None
247 ) -> Union[Self, Grouping[_T]]:
248 if against in (operators.any_op, operators.all_op, operators.getitem):
249 return expression.Grouping(self)
250 else:
251 return self
254class ARRAY(sqltypes.ARRAY[_T]):
255 """PostgreSQL ARRAY type.
257 The :class:`_postgresql.ARRAY` type is constructed in the same way
258 as the core :class:`_types.ARRAY` type; a member type is required, and a
259 number of dimensions is recommended if the type is to be used for more
260 than one dimension::
262 from sqlalchemy.dialects import postgresql
264 mytable = Table(
265 "mytable",
266 metadata,
267 Column("data", postgresql.ARRAY(Integer, dimensions=2)),
268 )
270 The :class:`_postgresql.ARRAY` type provides all operations defined on the
271 core :class:`_types.ARRAY` type, including support for "dimensions",
272 indexed access, and simple matching such as
273 :meth:`.types.ARRAY.Comparator.any` and
274 :meth:`.types.ARRAY.Comparator.all`. :class:`_postgresql.ARRAY`
275 class also
276 provides PostgreSQL-specific methods for containment operations, including
277 :meth:`.postgresql.ARRAY.Comparator.contains`
278 :meth:`.postgresql.ARRAY.Comparator.contained_by`, and
279 :meth:`.postgresql.ARRAY.Comparator.overlap`, e.g.::
281 mytable.c.data.contains([1, 2])
283 Indexed access is one-based by default, to match that of PostgreSQL;
284 for zero-based indexed access, set
285 :paramref:`_postgresql.ARRAY.zero_indexes`.
287 Additionally, the :class:`_postgresql.ARRAY`
288 type does not work directly in
289 conjunction with the :class:`.ENUM` type. For a workaround, see the
290 special type at :ref:`postgresql_array_of_enum`.
292 .. container:: topic
294 **Detecting Changes in ARRAY columns when using the ORM**
296 The :class:`_postgresql.ARRAY` type, when used with the SQLAlchemy ORM,
297 does not detect in-place mutations to the array. In order to detect
298 these, the :mod:`sqlalchemy.ext.mutable` extension must be used, using
299 the :class:`.MutableList` class::
301 from sqlalchemy.dialects.postgresql import ARRAY
302 from sqlalchemy.ext.mutable import MutableList
305 class SomeOrmClass(Base):
306 # ...
308 data = Column(MutableList.as_mutable(ARRAY(Integer)))
310 This extension will allow "in-place" changes such to the array
311 such as ``.append()`` to produce events which will be detected by the
312 unit of work. Note that changes to elements **inside** the array,
313 including subarrays that are mutated in place, are **not** detected.
315 Alternatively, assigning a new array value to an ORM element that
316 replaces the old one will always trigger a change event.
318 .. seealso::
320 :class:`_types.ARRAY` - base array type
322 :class:`_postgresql.array` - produces a literal array value.
324 """
326 def __init__(
327 self,
328 item_type: _CovariantTypeEngineArgument[_T],
329 as_tuple: bool = False,
330 dimensions: Optional[int] = None,
331 zero_indexes: bool = False,
332 ):
333 """Construct an ARRAY.
335 E.g.::
337 Column("myarray", ARRAY(Integer))
339 Arguments are:
341 :param item_type: The data type of items of this array. Note that
342 dimensionality is irrelevant here, so multi-dimensional arrays like
343 ``INTEGER[][]``, are constructed as ``ARRAY(Integer)``, not as
344 ``ARRAY(ARRAY(Integer))`` or such.
346 :param as_tuple=False: Specify whether return results
347 should be converted to tuples from lists. DBAPIs such
348 as psycopg2 return lists by default. When tuples are
349 returned, the results are hashable.
351 :param dimensions: if non-None, the ARRAY will assume a fixed
352 number of dimensions. This will cause the DDL emitted for this
353 ARRAY to include the exact number of bracket clauses ``[]``,
354 and will also optimize the performance of the type overall.
355 Note that PG arrays are always implicitly "non-dimensioned",
356 meaning they can store any number of dimensions no matter how
357 they were declared.
359 :param zero_indexes=False: when True, index values will be converted
360 between Python zero-based and PostgreSQL one-based indexes, e.g.
361 a value of one will be added to all index values before passing
362 to the database.
364 """
365 if isinstance(item_type, ARRAY):
366 raise ValueError(
367 "Do not nest ARRAY types; ARRAY(basetype) "
368 "handles multi-dimensional arrays of basetype"
369 )
370 self.item_type = sqltypes.to_instance(item_type)
371 self.as_tuple = as_tuple
372 self.dimensions = dimensions
373 self.zero_indexes = zero_indexes
375 class Comparator(sqltypes.ARRAY.Comparator[_CT]):
376 """Define comparison operations for :class:`_types.ARRAY`.
378 Note that these operations are in addition to those provided
379 by the base :class:`.types.ARRAY.Comparator` class, including
380 :meth:`.types.ARRAY.Comparator.any` and
381 :meth:`.types.ARRAY.Comparator.all`.
383 """
385 def contains(
386 self, other: typing_Any, **kwargs: typing_Any
387 ) -> ColumnElement[bool]:
388 """Boolean expression. Test if elements are a superset of the
389 elements of the argument array expression.
391 kwargs may be ignored by this operator but are required for API
392 conformance.
393 """
394 return self.operate(CONTAINS, other, result_type=sqltypes.Boolean)
396 def contained_by(self, other: typing_Any) -> ColumnElement[bool]:
397 """Boolean expression. Test if elements are a proper subset of the
398 elements of the argument array expression.
399 """
400 return self.operate(
401 CONTAINED_BY, other, result_type=sqltypes.Boolean
402 )
404 def overlap(self, other: typing_Any) -> ColumnElement[bool]:
405 """Boolean expression. Test if array has elements in common with
406 an argument array expression.
407 """
408 return self.operate(OVERLAP, other, result_type=sqltypes.Boolean)
410 comparator_factory = Comparator
412 @util.memoized_property
413 def _against_native_enum(self) -> bool:
414 return (
415 isinstance(self.item_type, sqltypes.Enum)
416 and self.item_type.native_enum
417 )
419 def literal_processor(
420 self, dialect: Dialect
421 ) -> Optional[_LiteralProcessorType[_T]]:
422 item_proc = self.item_type.dialect_impl(dialect).literal_processor(
423 dialect
424 )
425 if item_proc is None:
426 return None
428 def to_str(elements: Iterable[typing_Any]) -> str:
429 return f"ARRAY[{', '.join(elements)}]"
431 def process(value: Sequence[typing_Any]) -> str:
432 inner = self._apply_item_processor(
433 value, item_proc, self.dimensions, to_str
434 )
435 return inner
437 return process
439 def bind_processor(
440 self, dialect: Dialect
441 ) -> Optional[_BindProcessorType[Sequence[typing_Any]]]:
442 item_proc = self.item_type.dialect_impl(dialect).bind_processor(
443 dialect
444 )
446 def process(
447 value: Optional[Sequence[typing_Any]],
448 ) -> Optional[list[typing_Any]]:
449 if value is None:
450 return value
451 else:
452 return self._apply_item_processor(
453 value, item_proc, self.dimensions, list
454 )
456 return process
458 def result_processor(
459 self, dialect: Dialect, coltype: object
460 ) -> _ResultProcessorType[Sequence[typing_Any]]:
461 item_proc = self.item_type.dialect_impl(dialect).result_processor(
462 dialect, coltype
463 )
465 def process(
466 value: Sequence[typing_Any],
467 ) -> Optional[Sequence[typing_Any]]:
468 if value is None:
469 return value
470 else:
471 return self._apply_item_processor(
472 value,
473 item_proc,
474 self.dimensions,
475 tuple if self.as_tuple else list,
476 )
478 if self._against_native_enum:
479 super_rp = process
480 pattern = re.compile(r"^{(.*)}$")
482 def handle_raw_string(value: str) -> Sequence[Optional[str]]:
483 inner = pattern.match(value).group(1) # type: ignore[union-attr] # noqa: E501
484 return _split_enum_values(inner)
486 def process(
487 value: Sequence[typing_Any],
488 ) -> Optional[Sequence[typing_Any]]:
489 if value is None:
490 return value
491 # isinstance(value, str) is required to handle
492 # the case where a TypeDecorator for and Array of Enum is
493 # used like was required in sa < 1.3.17
494 return super_rp(
495 handle_raw_string(value)
496 if isinstance(value, str)
497 else value
498 )
500 return process
503def _split_enum_values(array_string: str) -> Sequence[Optional[str]]:
504 if '"' not in array_string:
505 # no escape char is present so it can just split on the comma
506 return [
507 r if r != "NULL" else None
508 for r in (array_string.split(",") if array_string else [])
509 ]
511 # handles quoted strings from:
512 # r'abc,"quoted","also\\\\quoted", "quoted, comma", "esc \" quot", qpr'
513 # returns
514 # ['abc', 'quoted', 'also\\quoted', 'quoted, comma', 'esc " quot', 'qpr']
515 text = array_string.replace(r"\"", "_$ESC_QUOTE$_")
516 text = text.replace(r"\\", "\\")
517 result = []
518 on_quotes = re.split(r'(")', text)
519 in_quotes = False
520 for tok in on_quotes:
521 if tok == '"':
522 in_quotes = not in_quotes
523 elif in_quotes:
524 result.append(tok.replace("_$ESC_QUOTE$_", '"'))
525 else:
526 # interpret NULL (without quotes!) as None
527 result.extend(
528 [
529 r if r != "NULL" else None
530 for r in re.findall(r"([^\s,]+),?", tok)
531 ]
532 )
533 return result