sqlalchemy.dialects.postgresql.array

class documentation

class array(expression.ClauseList, expression.ColumnElement):

A PostgreSQL ARRAY literal.

This is used to produce ARRAY literals in SQL expressions, e.g.:

from sqlalchemy.dialects.postgresql import array
from sqlalchemy.dialects import postgresql
from sqlalchemy import select, func

stmt = select(array([1,2]) + array([3,4,5]))

print(stmt.compile(dialect=postgresql.dialect()))

Produces the SQL:

SELECT ARRAY[%(param_1)s, %(param_2)s] ||
    ARRAY[%(param_3)s, %(param_4)s, %(param_5)s]) AS anon_1

An instance of .array will always have the datatype _types.ARRAY. The "inner" type of the array is inferred from the values present, unless the type_ keyword argument is passed:

array(['foo', 'bar'], type_=CHAR)

Multidimensional arrays are produced by nesting .array constructs. The dimensionality of the final _types.ARRAY type is calculated by recursively adding the dimensions of the inner _types.ARRAY type:

stmt = select(
    array([
        array([1, 2]), array([3, 4]), array([column('q'), column('x')])
    ])
)
print(stmt.compile(dialect=postgresql.dialect()))

Produces:

SELECT ARRAY[ARRAY[%(param_1)s, %(param_2)s],
ARRAY[%(param_3)s, %(param_4)s], ARRAY[q, x]] AS anon_1

New in version 1.3.6: added support for multidimensional array literals

See Also

_postgresql.ARRAY

Method	`__init__`	Undocumented
Method	`_bind_param`	Undocumented
Method	`self_group`	Apply a 'grouping' to this `_expression.ClauseElement`.
Class Variable	`__visit_name__`	Undocumented
Class Variable	`inherit_cache`	Indicate if this `.HasCacheKey` instance should make use of the cache key generation scheme used by its immediate superclass.
Class Variable	`stringify_dialect`	Undocumented
Instance Variable	`_type_tuple`	Undocumented
Instance Variable	`type`	Undocumented
Property	`_select_iterable`	Undocumented

Inherited from ClauseList:

Class Method	`_construct_raw`	Undocumented
Method	`__iter__`	Undocumented
Method	`__len__`	Undocumented
Method	`append`	Undocumented
Class Variable	`_is_clause_list`	Undocumented
Class Variable	`_traverse_internals`	Undocumented
Instance Variable	`_is_implicitly_boolean`	Undocumented
Instance Variable	`_text_converter_role`	Undocumented
Instance Variable	`clauses`	Undocumented
Instance Variable	`group`	Undocumented
Instance Variable	`group_contents`	Undocumented
Instance Variable	`operator`	Undocumented
Property	`_from_objects`	Undocumented

Inherited from InElementRole (via ClauseList):

Class Variable _role_name Undocumented

Inherited from SQLRole (via ClauseList, InElementRole):

Class Variable	`allows_lambda`	Undocumented
Class Variable	`uses_inspection`	Undocumented

Inherited from OrderByRole (via ClauseList):

Class Variable _role_name Undocumented

Inherited from AllowsLambdaRole (via ClauseList, OrderByRole):

Class Variable allows_lambda Undocumented

Inherited from SQLRole (via ClauseList, OrderByRole, ByOfRole, ColumnListRole):

Class Variable	`allows_lambda`	Undocumented
Class Variable	`uses_inspection`	Undocumented

Inherited from ColumnsClauseRole (via ClauseList):

Class Variable _role_name Undocumented

Inherited from AllowsLambdaRole (via ClauseList, ColumnsClauseRole):

Class Variable allows_lambda Undocumented

Inherited from UsesInspection (via ClauseList, ColumnsClauseRole):

Class Variable	`_post_inspect`	Undocumented
Class Variable	`uses_inspection`	Undocumented

Inherited from SQLRole (via ClauseList, ColumnsClauseRole, ColumnListRole):

Class Variable	`allows_lambda`	Undocumented
Class Variable	`uses_inspection`	Undocumented

Inherited from DMLColumnRole (via ClauseList):

Class Variable _role_name Undocumented

Inherited from SQLRole (via ClauseList, DMLColumnRole):

Class Variable	`allows_lambda`	Undocumented
Class Variable	`uses_inspection`	Undocumented

Inherited from ClauseElement (via ClauseList):

Method	`compare`	Compare this `_expression.ClauseElement` to the given `_expression.ClauseElement`.
Method	`compile`	Compile this SQL expression.
Method	`params`	Return a copy with `_expression.bindparam` elements replaced.
Method	`unique_params`	Return a copy with `_expression.bindparam` elements replaced.
Method	`__bool__`	Undocumented
Method	`__getstate__`	Undocumented
Method	`__invert__`	Undocumented
Method	`__repr__`	Undocumented
Method	`__str__`	Undocumented
Method	`_clone`	Create a shallow copy of this ClauseElement.
Method	`_cloned_set`	Return the set consisting all cloned ancestors of this ClauseElement.
Method	`_compile_w_cache`	Undocumented
Method	`_compiler`	Return a compiler appropriate for this ClauseElement, given a Dialect.
Method	`_execute_on_connection`	Undocumented
Method	`_negate`	Undocumented
Method	`_negate_in_binary`	a hook to allow the right side of a binary expression to respond to a negation of the binary expression.
Method	`_replace_params`	Undocumented
Method	`_set_propagate_attrs`	Undocumented
Method	`_ungroup`	Return this `_expression.ClauseElement` without any groupings.
Method	`_with_binary_element_type`	in the context of binary expression, convert the type of this object to the one given.
Class Variable	`_cache_key_traversal`	Undocumented
Class Variable	`_is_bind_parameter`	Undocumented
Class Variable	`_is_clone_of`	Undocumented
Class Variable	`_is_from_clause`	Undocumented
Class Variable	`_is_from_container`	Undocumented
Class Variable	`_is_immutable`	Undocumented
Class Variable	`_is_lambda_element`	Undocumented
Class Variable	`_is_returns_rows`	Undocumented
Class Variable	`_is_select_container`	Undocumented
Class Variable	`_is_select_statement`	Undocumented
Class Variable	`_is_singleton_constant`	Undocumented
Class Variable	`_is_text_clause`	Undocumented
Class Variable	`_is_textual`	Undocumented
Class Variable	`_order_by_label_element`	Undocumented
Class Variable	`bind`	Undocumented
Class Variable	`description`	Undocumented
Class Variable	`is_clause_element`	Undocumented
Class Variable	`is_selectable`	Undocumented
Class Variable	`supports_execution`	Undocumented
Instance Variable	`_propagate_attrs`	like annotations, however these propagate outwards liberally as SQL constructs are built, and are set up at construction time.
Property	`_constructor`	return the 'constructor' for this ClauseElement.
Property	`entity_namespace`	Undocumented

Inherited from SQLRole (via ClauseList, ClauseElement):

Class Variable	`allows_lambda`	Undocumented
Class Variable	`uses_inspection`	Undocumented

Inherited from SupportsWrappingAnnotations (via ClauseList, ClauseElement):

Method	`_annotate`	return a copy of this ClauseElement with annotations updated by the given dictionary.
Method	`_deannotate`	return a copy of this `_expression.ClauseElement` with annotations removed.
Method	`_with_annotations`	return a copy of this ClauseElement with annotations replaced by the given dictionary.

Inherited from SupportsAnnotations (via ClauseList, ClauseElement, SupportsWrappingAnnotations):

Property _annotations_cache_key Undocumented

Inherited from MemoizedHasCacheKey (via ClauseList, ClauseElement):

Method _generate_cache_key return a cache key.

Inherited from HasCacheKey (via ClauseList, ClauseElement, MemoizedHasCacheKey):

Class Method	`_generate_cache_attrs`	generate cache key dispatcher for a new class.
Class Method	`_generate_cache_key_for_object`	Undocumented
Method	`_gen_cache_key`	return an optional cache key.
Class Variable	`__slots__`	Undocumented
Class Variable	`_hierarchy_supports_caching`	private attribute which may be set to False to prevent the inherit_cache warning from being emitted for a hierarchy of subclasses.
Class Variable	`_is_has_cache_key`	Undocumented

Inherited from HasCopyInternals (via ClauseList, ClauseElement):

Method _copy_internals Reassign internal elements to be clones of themselves.

Inherited from Traversible (via ClauseList, ClauseElement):

Method	`get_children`	Return immediate child `.visitors.Traversible` elements of this `.visitors.Traversible`.
Method	`__class_getitem__`	Undocumented

Inherited from ColumnElement:

Method	`cast`	Produce a type cast, i.e. `CAST(<expression> AS <type>)`.
Method	`label`	Produce a column label, i.e. `<columnname> AS <name>`.
Method	`operate`	Operate on an argument.
Method	`reverse_operate`	Reverse operate on an argument.
Method	`shares_lineage`	Return True if the given `_expression.ColumnElement` has a common ancestor to this `_expression.ColumnElement`.
Class Variable	`foreign_keys`	Undocumented
Class Variable	`key`	The 'key' that in some circumstances refers to this object in a Python namespace.
Class Variable	`primary_key`	Undocumented
Method	`__getattr__`	Undocumented
Method	`_anon_label`	Undocumented
Method	`_compare_name_for_result`	Return True if the given column element compares to this one when targeting within a result row.
Method	`_dedupe_anon_label_idx`	label to apply to a column that is anon labeled, but repeated in the SELECT, so that we have to make an "extra anon" label that disambiguates it from the previous appearance.
Method	`_dedupe_anon_tq_label_idx`	Undocumented
Method	`_expression_label`	a suggested label to use in the case that the column has no name, which should be used if possible as the explicit 'AS <label>' where this expression would normally have an anon label.
Method	`_make_proxy`	Create a new `_expression.ColumnElement` representing this `_expression.ColumnElement` as it appears in the select list of a descending selectable.
Method	`_negate`	Undocumented
Method	`_proxy_key`	Undocumented
Method	`_tq_key_label`	A label-based version of 'key' that in some circumstances refers to this object in a Python namespace.
Method	`_uncached_proxy_set`	An 'uncached' version of proxy set.
Method	`comparator`	Undocumented
Class Variable	`_allow_label_resolve`	A flag that can be flipped to prevent a column from being resolvable by string label name.
Class Variable	`_alt_names`	Undocumented
Class Variable	`_is_implicitly_boolean`	Undocumented
Class Variable	`_proxies`	Undocumented
Class Variable	`_render_label_in_columns_clause`	No summary
Class Variable	`_tq_label`	The named label that can be used to target this column in a result set in a "table qualified" context.
Property	`_anon_key_label`	Provides a constant 'anonymous key label' for this ColumnElement.
Property	`_anon_name_label`	Provides a constant 'anonymous label' for this ColumnElement.
Property	`_anon_tq_key_label`	Undocumented
Property	`_anon_tq_label`	Undocumented
Property	`_key_label`	legacy; renamed to _tq_key_label
Property	`_label`	legacy; renamed to _tq_label
Property	`_non_anon_label`	the 'name' that naturally applies this element when rendered in SQL.
Property	`anon_key_label`	Undocumented
Property	`anon_label`	Undocumented
Property	`base_columns`	Undocumented
Property	`expression`	Return a column expression.
Property	`proxy_set`	Undocumented

Inherited from ColumnArgumentOrKeyRole (via ColumnElement):

Class Variable _role_name Undocumented

Inherited from SQLRole (via ColumnElement, ColumnArgumentOrKeyRole, ColumnArgumentRole):

Class Variable	`allows_lambda`	Undocumented
Class Variable	`uses_inspection`	Undocumented

Inherited from StatementOptionRole (via ColumnElement):

Class Variable _role_name Undocumented

Inherited from SQLRole (via ColumnElement, StatementOptionRole, StructuralRole):

Class Variable	`allows_lambda`	Undocumented
Class Variable	`uses_inspection`	Undocumented

Inherited from WhereHavingRole (via ColumnElement):

Class Variable _role_name Undocumented

Inherited from AllowsLambdaRole (via ColumnElement, WhereHavingRole, OnClauseRole):

Class Variable allows_lambda Undocumented

Inherited from SQLRole (via ColumnElement, WhereHavingRole, OnClauseRole, StructuralRole):

Class Variable	`allows_lambda`	Undocumented
Class Variable	`uses_inspection`	Undocumented

Inherited from BinaryElementRole (via ColumnElement):

Class Variable _role_name Undocumented

Inherited from SQLRole (via ColumnElement, BinaryElementRole, ExpressionElementRole):

Class Variable	`allows_lambda`	Undocumented
Class Variable	`uses_inspection`	Undocumented

Inherited from OrderByRole (via ColumnElement):

Class Variable _role_name Undocumented

Inherited from AllowsLambdaRole (via ColumnElement, OrderByRole):

Class Variable allows_lambda Undocumented

Inherited from SQLRole (via ColumnElement, OrderByRole, ByOfRole, ColumnListRole):

Class Variable	`allows_lambda`	Undocumented
Class Variable	`uses_inspection`	Undocumented

Inherited from ColumnsClauseRole (via ColumnElement):

Class Variable _role_name Undocumented

Inherited from AllowsLambdaRole (via ColumnElement, ColumnsClauseRole):

Class Variable allows_lambda Undocumented

Inherited from UsesInspection (via ColumnElement, ColumnsClauseRole):

Class Variable	`_post_inspect`	Undocumented
Class Variable	`uses_inspection`	Undocumented

Inherited from SQLRole (via ColumnElement, ColumnsClauseRole, ColumnListRole):

Class Variable	`allows_lambda`	Undocumented
Class Variable	`uses_inspection`	Undocumented

Inherited from LimitOffsetRole (via ColumnElement):

Class Variable _role_name Undocumented

Inherited from SQLRole (via ColumnElement, LimitOffsetRole):

Class Variable	`allows_lambda`	Undocumented
Class Variable	`uses_inspection`	Undocumented

Inherited from DMLColumnRole (via ColumnElement):

Class Variable _role_name Undocumented

Inherited from SQLRole (via ColumnElement, DMLColumnRole):

Class Variable	`allows_lambda`	Undocumented
Class Variable	`uses_inspection`	Undocumented

Inherited from DDLConstraintColumnRole (via ColumnElement):

Class Variable _role_name Undocumented

Inherited from SQLRole (via ColumnElement, DDLConstraintColumnRole):

Class Variable	`allows_lambda`	Undocumented
Class Variable	`uses_inspection`	Undocumented

Inherited from DDLExpressionRole (via ColumnElement):

Class Variable _role_name Undocumented

Inherited from SQLRole (via ColumnElement, DDLExpressionRole, StructuralRole):

Class Variable	`allows_lambda`	Undocumented
Class Variable	`uses_inspection`	Undocumented

Inherited from ColumnOperators (via ColumnElement):

Method	`__add__`	Implement the `+` operator.
Method	`__contains__`	Undocumented
Method	`__div__`	Implement the `/` operator.
Method	`__eq__`	Implement the `==` operator.
Method	`__ge__`	Implement the `>=` operator.
Method	`__getitem__`	Implement the [] operator.
Method	`__gt__`	Implement the `>` operator.
Method	`__le__`	Implement the `<=` operator.
Method	`__lshift__`	implement the << operator.
Method	`__lt__`	Implement the `<` operator.
Method	`__mod__`	Implement the `%` operator.
Method	`__mul__`	Implement the `*` operator.
Method	`__ne__`	Implement the `!=` operator.
Method	`__neg__`	Implement the `-` operator.
Method	`__radd__`	Implement the `+` operator in reverse.
Method	`__rdiv__`	Implement the `/` operator in reverse.
Method	`__rmod__`	Implement the `%` operator in reverse.
Method	`__rmul__`	Implement the `*` operator in reverse.
Method	`__rshift__`	implement the >> operator.
Method	`__rsub__`	Implement the `-` operator in reverse.
Method	`__rtruediv__`	Implement the `//` operator in reverse.
Method	`__sub__`	Implement the `-` operator.
Method	`__truediv__`	Implement the `//` operator.
Method	`all_`	Produce an `_expression.all_` clause against the parent object.
Method	`any_`	Produce an `_expression.any_` clause against the parent object.
Method	`asc`	Produce a `_expression.asc` clause against the parent object.
Method	`between`	Produce a `_expression.between` clause against the parent object, given the lower and upper range.
Method	`collate`	Produce a `_expression.collate` clause against the parent object, given the collation string.
Method	`concat`	Implement the 'concat' operator.
Method	`contains`	Implement the 'contains' operator.
Method	`desc`	Produce a `_expression.desc` clause against the parent object.
Method	`distinct`	Produce a `_expression.distinct` clause against the parent object.
Method	`endswith`	Implement the 'endswith' operator.
Method	`ilike`	Implement the `ilike` operator, e.g. case insensitive LIKE.
Method	`in_`	Implement the `in` operator.
Method	`is_`	Implement the `IS` operator.
Method	`is_distinct_from`	Implement the `IS DISTINCT FROM` operator.
Method	`is_not`	Implement the `IS NOT` operator.
Method	`is_not_distinct_from`	Implement the `IS NOT DISTINCT FROM` operator.
Method	`like`	Implement the `like` operator.
Method	`match`	Implements a database-specific 'match' operator.
Method	`not_ilike`	implement the `NOT ILIKE` operator.
Method	`not_in`	implement the `NOT IN` operator.
Method	`not_like`	implement the `NOT LIKE` operator.
Method	`nulls_first`	Produce a `_expression.nulls_first` clause against the parent object.
Method	`nulls_last`	Produce a `_expression.nulls_last` clause against the parent object.
Method	`regexp_match`	Implements a database-specific 'regexp match' operator.
Method	`regexp_replace`	Implements a database-specific 'regexp replace' operator.
Method	`startswith`	Implement the `startswith` operator.
Class Variable	`__slots__`	Undocumented
Class Variable	`timetuple`	Hack, allows datetime objects to be compared on the LHS.

Inherited from Operators (via ColumnElement, ColumnOperators):

Method	`__and__`	Implement the `&` operator.
Method	`__invert__`	Implement the `~` operator.
Method	`__or__`	Implement the `\|` operator.
Method	`bool_op`	Return a custom boolean operator.
Method	`op`	Produce a generic operator function.

Inherited from ClauseElement (via ColumnElement):

Method	`compare`	Compare this `_expression.ClauseElement` to the given `_expression.ClauseElement`.
Method	`compile`	Compile this SQL expression.
Method	`params`	Return a copy with `_expression.bindparam` elements replaced.
Method	`unique_params`	Return a copy with `_expression.bindparam` elements replaced.
Method	`__bool__`	Undocumented
Method	`__getstate__`	Undocumented
Method	`__invert__`	Undocumented
Method	`__repr__`	Undocumented
Method	`__str__`	Undocumented
Method	`_clone`	Create a shallow copy of this ClauseElement.
Method	`_cloned_set`	Return the set consisting all cloned ancestors of this ClauseElement.
Method	`_compile_w_cache`	Undocumented
Method	`_compiler`	Return a compiler appropriate for this ClauseElement, given a Dialect.
Method	`_execute_on_connection`	Undocumented
Method	`_negate_in_binary`	a hook to allow the right side of a binary expression to respond to a negation of the binary expression.
Method	`_replace_params`	Undocumented
Method	`_set_propagate_attrs`	Undocumented
Method	`_ungroup`	Return this `_expression.ClauseElement` without any groupings.
Method	`_with_binary_element_type`	in the context of binary expression, convert the type of this object to the one given.
Class Variable	`_cache_key_traversal`	Undocumented
Class Variable	`_from_objects`	Undocumented
Class Variable	`_is_bind_parameter`	Undocumented
Class Variable	`_is_clause_list`	Undocumented
Class Variable	`_is_clone_of`	Undocumented
Class Variable	`_is_from_clause`	Undocumented
Class Variable	`_is_from_container`	Undocumented
Class Variable	`_is_immutable`	Undocumented
Class Variable	`_is_lambda_element`	Undocumented
Class Variable	`_is_returns_rows`	Undocumented
Class Variable	`_is_select_container`	Undocumented
Class Variable	`_is_select_statement`	Undocumented
Class Variable	`_is_singleton_constant`	Undocumented
Class Variable	`_is_text_clause`	Undocumented
Class Variable	`_is_textual`	Undocumented
Class Variable	`_order_by_label_element`	Undocumented
Class Variable	`bind`	Undocumented
Class Variable	`description`	Undocumented
Class Variable	`is_clause_element`	Undocumented
Class Variable	`is_selectable`	Undocumented
Class Variable	`supports_execution`	Undocumented
Instance Variable	`_propagate_attrs`	like annotations, however these propagate outwards liberally as SQL constructs are built, and are set up at construction time.
Property	`_constructor`	return the 'constructor' for this ClauseElement.
Property	`entity_namespace`	Undocumented

Inherited from SQLRole (via ColumnElement, ClauseElement):

Class Variable	`allows_lambda`	Undocumented
Class Variable	`uses_inspection`	Undocumented

Inherited from SupportsWrappingAnnotations (via ColumnElement, ClauseElement):

Method	`_annotate`	return a copy of this ClauseElement with annotations updated by the given dictionary.
Method	`_deannotate`	return a copy of this `_expression.ClauseElement` with annotations removed.
Method	`_with_annotations`	return a copy of this ClauseElement with annotations replaced by the given dictionary.

Inherited from SupportsAnnotations (via ColumnElement, ClauseElement, SupportsWrappingAnnotations):

Property _annotations_cache_key Undocumented

Inherited from MemoizedHasCacheKey (via ColumnElement, ClauseElement):

Method _generate_cache_key return a cache key.

Inherited from HasCacheKey (via ColumnElement, ClauseElement, MemoizedHasCacheKey):

Class Method	`_generate_cache_attrs`	generate cache key dispatcher for a new class.
Class Method	`_generate_cache_key_for_object`	Undocumented
Method	`_gen_cache_key`	return an optional cache key.
Class Variable	`__slots__`	Undocumented
Class Variable	`_hierarchy_supports_caching`	private attribute which may be set to False to prevent the inherit_cache warning from being emitted for a hierarchy of subclasses.
Class Variable	`_is_has_cache_key`	Undocumented

Inherited from HasCopyInternals (via ColumnElement, ClauseElement):

Method _copy_internals Reassign internal elements to be clones of themselves.

Inherited from Traversible (via ColumnElement, ClauseElement):

Method	`get_children`	Return immediate child `.visitors.Traversible` elements of this `.visitors.Traversible`.
Method	`__class_getitem__`	Undocumented

def __init__(self, clauses, **kw):

overrides sqlalchemy.sql.elements.ClauseList.__init__

Undocumented

def _bind_param(self, operator, obj, _assume_scalar=False, type_=None):

overrides sqlalchemy.sql.expression.ColumnElement._bind_param

Undocumented

def self_group(self, against=None):

overrides sqlalchemy.sql.elements.ClauseList.self_group

Apply a 'grouping' to this _expression.ClauseElement.

This method is overridden by subclasses to return a "grouping" construct, i.e. parenthesis. In particular it's used by "binary" expressions to provide a grouping around themselves when placed into a larger expression, as well as by _expression.select constructs when placed into the FROM clause of another _expression.select. (Note that subqueries should be normally created using the _expression.Select.alias method, as many platforms require nested SELECT statements to be named).

As expressions are composed together, the application of self_group is automatic - end-user code should never need to use this method directly. Note that SQLAlchemy's clause constructs take operator precedence into account - so parenthesis might not be needed, for example, in an expression like x OR (y AND z) - AND takes precedence over OR.

The base self_group method of _expression.ClauseElement just returns self.

__visit_name__: str =

overrides sqlalchemy.sql.elements.ClauseList.__visit_name__

Undocumented

inherit_cache: bool =

overrides sqlalchemy.sql.traversals.HasCacheKey.inherit_cache

Indicate if this .HasCacheKey instance should make use of the cache key generation scheme used by its immediate superclass.

The attribute defaults to None, which indicates that a construct has not yet taken into account whether or not its appropriate for it to participate in caching; this is functionally equivalent to setting the value to False, except that a warning is also emitted.

This flag can be set to True on a particular class, if the SQL that corresponds to the object does not change based on attributes which are local to this class, and not its superclass.