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

2 files changed, 16 insertions, 11 deletions

diff --git a/lib/sqlalchemy/dialects/postgresql/array.py b/lib/sqlalchemy/dialects/postgresql/array.py
index 91bb89ea9..c2d99845f 100644
--- a/lib/sqlalchemy/dialects/postgresql/array.py
+++ b/lib/sqlalchemy/dialects/postgresql/array.py
@@ -331,12 +331,6 @@ class ARRAY(sqltypes.ARRAY):
             )
 
     @util.memoized_property
-    def _require_cast(self):
-        return self._against_native_enum or isinstance(
-            self.item_type, sqltypes.JSON
-        )
-
-    @util.memoized_property
     def _against_native_enum(self):
         return (
             isinstance(self.item_type, sqltypes.Enum)
@@ -344,10 +338,7 @@ class ARRAY(sqltypes.ARRAY):
         )
 
     def bind_expression(self, bindvalue):
-        if self._require_cast:
-            return expression.cast(bindvalue, self)
-        else:
-            return bindvalue
+        return bindvalue
 
     def bind_processor(self, dialect):
         item_proc = self.item_type.dialect_impl(dialect).bind_processor(
diff --git a/lib/sqlalchemy/dialects/postgresql/psycopg2.py b/lib/sqlalchemy/dialects/postgresql/psycopg2.py
index a52eacd8b..1969eb844 100644
--- a/lib/sqlalchemy/dialects/postgresql/psycopg2.py
+++ b/lib/sqlalchemy/dialects/postgresql/psycopg2.py
@@ -450,6 +450,7 @@ from ... import processors
 from ... import types as sqltypes
 from ... import util
 from ...engine import cursor as _cursor
+from ...sql import elements
 from ...util import collections_abc
 
 
@@ -597,7 +598,20 @@ class PGExecutionContext_psycopg2(PGExecutionContext):
 
 
 class PGCompiler_psycopg2(PGCompiler):
-    pass
+    def visit_bindparam(self, bindparam, skip_bind_expression=False, **kw):
+
+        text = super(PGCompiler_psycopg2, self).visit_bindparam(
+            bindparam, skip_bind_expression=skip_bind_expression, **kw
+        )
+        # note that if the type has a bind_expression(), we will get a
+        # double compile here
+        if not skip_bind_expression and bindparam.type._is_array:
+            text += "::%s" % (
+                elements.TypeClause(bindparam.type)._compiler_dispatch(
+                    self, skip_bind_expression=skip_bind_expression, **kw
+                ),
+            )
+        return text
 
 
 class PGIdentifierPreparer_psycopg2(PGIdentifierPreparer):