From dfa1d3b28f1a0abf1e11c76a94f7a65bf98d29af Mon Sep 17 00:00:00 2001 From: Federico Caselli Date: Wed, 10 Mar 2021 23:54:52 +0100 Subject: CAST the elements in ARRAYs when using psycopg2 Adjusted the psycopg2 dialect to emit an explicit PostgreSQL-style cast for bound parameters that contain ARRAY elements. This allows the full range of datatypes to function correctly within arrays. The asyncpg dialect already generated these internal casts in the final statement. This also includes support for array slice updates as well as the PostgreSQL-specific :meth:`_postgresql.ARRAY.contains` method. Fixes: #6023 Change-Id: Ia7519ac4371a635f05ac69a3a4d0f4e6d2f04cad --- lib/sqlalchemy/sql/crud.py | 9 +++++++++ lib/sqlalchemy/sql/sqltypes.py | 2 ++ lib/sqlalchemy/sql/type_api.py | 1 + 3 files changed, 12 insertions(+) (limited to 'lib/sqlalchemy/sql') diff --git a/lib/sqlalchemy/sql/crud.py b/lib/sqlalchemy/sql/crud.py index 8b4950aa3..174a1c131 100644 --- a/lib/sqlalchemy/sql/crud.py +++ b/lib/sqlalchemy/sql/crud.py @@ -943,6 +943,9 @@ def _get_stmt_parameter_tuples_params( # add it to values() in an "as-is" state, # coercing right side to bound param + # note one of the main use cases for this is array slice + # updates on PostgreSQL, as the left side is also an expression. + col_expr = compiler.process( k, include_table=compile_state.include_table_with_column_exprs ) @@ -952,6 +955,12 @@ def _get_stmt_parameter_tuples_params( elements.BindParameter(None, v, type_=k.type), **kw ) else: + if v._is_bind_parameter and v.type._isnull: + # either unique parameter, or other bound parameters that + # were passed in directly + # set type to that of the column unconditionally + v = v._with_binary_element_type(k.type) + v = compiler.process(v.self_group(), **kw) values.append((k, col_expr, v)) diff --git a/lib/sqlalchemy/sql/sqltypes.py b/lib/sqlalchemy/sql/sqltypes.py index d075ef77d..816423d1b 100644 --- a/lib/sqlalchemy/sql/sqltypes.py +++ b/lib/sqlalchemy/sql/sqltypes.py @@ -2675,6 +2675,8 @@ class ARRAY(SchemaEventTarget, Indexable, Concatenable, TypeEngine): __visit_name__ = "ARRAY" + _is_array = True + zero_indexes = False """If True, Python zero-based indexes should be interpreted as one-based on the SQL expression side.""" diff --git a/lib/sqlalchemy/sql/type_api.py b/lib/sqlalchemy/sql/type_api.py index 46751cb22..9752750c5 100644 --- a/lib/sqlalchemy/sql/type_api.py +++ b/lib/sqlalchemy/sql/type_api.py @@ -47,6 +47,7 @@ class TypeEngine(Traversible): _isnull = False _is_tuple_type = False _is_table_value = False + _is_array = False class Comparator(operators.ColumnOperators): """Base class for custom comparison operations defined at the -- cgit v1.2.1