Correct for Variant + ARRAY cases in psycopg2

Fixed regression caused by :ticket:`6023` where the PostgreSQL cast
operator applied to elements within an :class:`_types.ARRAY` when using
psycopg2 would fail to use the correct type in the case that the datatype
were also embedded within an instance of the :class:`_types.Variant`
adapter.

Additionally, repairs support for the correct CREATE TYPE to be emitted
when using a ``Variant(ARRAY(some_schema_type))``.

Fixes: #6182
Change-Id: I1b9ba7c876980d4650715a0b0801b46bdc72860d

1 files changed, 11 insertions, 6 deletions

diff --git a/lib/sqlalchemy/dialects/postgresql/psycopg2.py b/lib/sqlalchemy/dialects/postgresql/psycopg2.py
index 1969eb844..16f9ecefa 100644
--- a/lib/sqlalchemy/dialects/postgresql/psycopg2.py
+++ b/lib/sqlalchemy/dialects/postgresql/psycopg2.py
@@ -605,12 +605,17 @@ class PGCompiler_psycopg2(PGCompiler):
         )
         # 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
-                ),
-            )
+        if not skip_bind_expression and (
+            bindparam.type._is_array or bindparam.type._is_type_decorator
+        ):
+            typ = bindparam.type._unwrapped_dialect_impl(self.dialect)
+
+            if typ._is_array:
+                text += "::%s" % (
+                    elements.TypeClause(typ)._compiler_dispatch(
+                        self, skip_bind_expression=skip_bind_expression, **kw
+                    ),
+                )
         return text