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

1 files changed, 9 insertions, 0 deletions

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))