- Repaired the :class:`.ExcludeConstraint` construct to support common

features that other objects like :class:`.Index` now do, that
the column expression may be specified as an arbitrary SQL
expression such as :obj:`.cast` or :obj:`.text`.
fixes #3454

5 files changed, 108 insertions, 22 deletions

diff --git a/doc/build/changelog/changelog_10.rst b/doc/build/changelog/changelog_10.rst
index f4c1d215d..b30111129 100644
--- a/doc/build/changelog/changelog_10.rst
+++ b/doc/build/changelog/changelog_10.rst
@@ -19,6 +19,15 @@
     :version: 1.0.6
 
     .. change::
+        :tags: bug, postgresql
+        :tickets: 3454
+
+        Repaired the :class:`.ExcludeConstraint` construct to support common
+        features that other objects like :class:`.Index` now do, that
+        the column expression may be specified as an arbitrary SQL
+        expression such as :obj:`.cast` or :obj:`.text`.
+
+    .. change::
         :tags: feature, postgresql
         :pullreq: github:182
 
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index 73fe5022a..bc1c3614e 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -1601,15 +1601,17 @@ class PGDDLCompiler(compiler.DDLCompiler):
             text += " WHERE " + where_compiled
         return text
 
-    def visit_exclude_constraint(self, constraint):
+    def visit_exclude_constraint(self, constraint, **kw):
         text = ""
         if constraint.name is not None:
             text += "CONSTRAINT %s " % \
                     self.preparer.format_constraint(constraint)
         elements = []
-        for c in constraint.columns:
-            op = constraint.operators[c.name]
-            elements.append(self.preparer.quote(c.name) + ' WITH ' + op)
+        for expr, name, op in constraint._render_exprs:
+            kw['include_table'] = False
+            elements.append(
+                "%s WITH %s" % (self.sql_compiler.process(expr, **kw), op)
+            )
         text += "EXCLUDE USING %s (%s)" % (constraint.using,
                                            ', '.join(elements))
         if constraint.where is not None:
diff --git a/lib/sqlalchemy/dialects/postgresql/constraints.py b/lib/sqlalchemy/dialects/postgresql/constraints.py
index 0371daf3d..4cfc050de 100644
--- a/lib/sqlalchemy/dialects/postgresql/constraints.py
+++ b/lib/sqlalchemy/dialects/postgresql/constraints.py
@@ -3,8 +3,9 @@
 #
 # This module is part of SQLAlchemy and is released under
 # the MIT License: http://www.opensource.org/licenses/mit-license.php
-from sqlalchemy.schema import ColumnCollectionConstraint
-from sqlalchemy.sql import expression
+from ...sql.schema import ColumnCollectionConstraint
+from ...sql import expression
+from ... import util
 
 
 class ExcludeConstraint(ColumnCollectionConstraint):
@@ -48,17 +49,39 @@ static/sql-createtable.html#SQL-CREATETABLE-EXCLUDE
           for this constraint.
 
         """
+        columns = []
+        render_exprs = []
+        self.operators = {}
+
+        expressions, operators = zip(*elements)
+
+        for (expr, column, strname, add_element), operator in zip(
+                self._extract_col_expression_collection(expressions),
+                operators
+        ):
+            if add_element is not None:
+                columns.append(add_element)
+
+            name = column.name if column is not None else strname
+
+            if name is not None:
+                # backwards compat
+                self.operators[name] = operator
+
+            expr = expression._literal_as_text(expr)
+
+            render_exprs.append(
+                (expr, name, operator)
+            )
+
+        self._render_exprs = render_exprs
         ColumnCollectionConstraint.__init__(
             self,
-            *[col for col, op in elements],
+            *columns,
             name=kw.get('name'),
             deferrable=kw.get('deferrable'),
             initially=kw.get('initially')
         )
-        self.operators = {}
-        for col_or_string, op in elements:
-            name = getattr(col_or_string, 'name', col_or_string)
-            self.operators[name] = op
         self.using = kw.get('using', 'gist')
         where = kw.get('where')
         if where:
diff --git a/lib/sqlalchemy/sql/schema.py b/lib/sqlalchemy/sql/schema.py
index e6d1d8858..a8989627d 100644
--- a/lib/sqlalchemy/sql/schema.py
+++ b/lib/sqlalchemy/sql/schema.py
@@ -2392,6 +2392,22 @@ class ColumnCollectionMixin(object):
         if _autoattach and self._pending_colargs:
             self._check_attach()
 
+    @classmethod
+    def _extract_col_expression_collection(cls, expressions):
+        for expr in expressions:
+            strname = None
+            column = None
+            if not isinstance(expr, ClauseElement):
+                # this assumes a string
+                strname = expr
+            else:
+                cols = []
+                visitors.traverse(expr, {}, {'column': cols.append})
+                if cols:
+                    column = cols[0]
+            add_element = column if column is not None else strname
+            yield expr, column, strname, add_element
+
     def _check_attach(self, evt=False):
         col_objs = [
             c for c in self._pending_colargs
@@ -3086,14 +3102,10 @@ class Index(DialectKWArgs, ColumnCollectionMixin, SchemaItem):
         self.table = None
 
         columns = []
-        for expr in expressions:
-            if not isinstance(expr, ClauseElement):
-                columns.append(expr)
-            else:
-                cols = []
-                visitors.traverse(expr, {}, {'column': cols.append})
-                if cols:
-                    columns.append(cols[0])
+        for expr, column, strname, add_element in self.\
+                _extract_col_expression_collection(expressions):
+            if add_element is not None:
+                columns.append(add_element)
 
         self.expressions = expressions
         self.name = quoted_name(name, kw.pop("quote", None))
diff --git a/test/dialect/postgresql/test_compiler.py b/test/dialect/postgresql/test_compiler.py
index aa3f80fdc..d5c8d9065 100644
--- a/test/dialect/postgresql/test_compiler.py
+++ b/test/dialect/postgresql/test_compiler.py
@@ -5,7 +5,8 @@ from sqlalchemy.testing.assertions import AssertsCompiledSQL, is_, \
 from sqlalchemy.testing import engines, fixtures
 from sqlalchemy import testing
 from sqlalchemy import Sequence, Table, Column, Integer, update, String,\
-    insert, func, MetaData, Enum, Index, and_, delete, select, cast, text
+    insert, func, MetaData, Enum, Index, and_, delete, select, cast, text, \
+    Text
 from sqlalchemy.dialects.postgresql import ExcludeConstraint, array
 from sqlalchemy import exc, schema
 from sqlalchemy.dialects.postgresql import base as postgresql
@@ -443,8 +444,47 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
         tbl.append_constraint(cons_copy)
         self.assert_compile(schema.AddConstraint(cons_copy),
                             'ALTER TABLE testtbl ADD EXCLUDE USING gist '
-                            '(room WITH =)',
-                            dialect=postgresql.dialect())
+                            '(room WITH =)')
+
+    def test_exclude_constraint_text(self):
+        m = MetaData()
+        cons = ExcludeConstraint((text('room::TEXT'), '='))
+        Table(
+            'testtbl', m,
+            Column('room', String),
+            cons)
+        self.assert_compile(
+            schema.AddConstraint(cons),
+            'ALTER TABLE testtbl ADD EXCLUDE USING gist '
+            '(room::TEXT WITH =)')
+
+    def test_exclude_constraint_cast(self):
+        m = MetaData()
+        tbl = Table(
+            'testtbl', m,
+            Column('room', String)
+        )
+        cons = ExcludeConstraint((cast(tbl.c.room, Text), '='))
+        tbl.append_constraint(cons)
+        self.assert_compile(
+            schema.AddConstraint(cons),
+            'ALTER TABLE testtbl ADD EXCLUDE USING gist '
+            '(CAST(room AS TEXT) WITH =)'
+        )
+
+    def test_exclude_constraint_cast_quote(self):
+        m = MetaData()
+        tbl = Table(
+            'testtbl', m,
+            Column('Room', String)
+        )
+        cons = ExcludeConstraint((cast(tbl.c.Room, Text), '='))
+        tbl.append_constraint(cons)
+        self.assert_compile(
+            schema.AddConstraint(cons),
+            'ALTER TABLE testtbl ADD EXCLUDE USING gist '
+            '(CAST("Room" AS TEXT) WITH =)'
+        )
 
     def test_substring(self):
         self.assert_compile(func.substring('abc', 1, 2),