From a7f3dad6c93ab43a11521a2ecd240b5e023367fc Mon Sep 17 00:00:00 2001
From: Federico Caselli <cfederico87@gmail.com>
Date: Mon, 27 Feb 2023 22:47:19 +0100
Subject: [PATCH] Improve exclude constraint docs and tests.

Follow up of 71693c94d52612a5e88128575ff308ee4a923c00

Change-Id: Icc9d9942bda92171581dec82cf0cacbd3e3e4162
---
 lib/sqlalchemy/dialects/postgresql/ext.py | 14 ++++++++-----
 test/dialect/postgresql/test_compiler.py  | 25 +++++++++++++++++------
 2 files changed, 28 insertions(+), 11 deletions(-)

diff --git a/lib/sqlalchemy/dialects/postgresql/ext.py b/lib/sqlalchemy/dialects/postgresql/ext.py
index 22604955dc..f382af86f3 100644
--- a/lib/sqlalchemy/dialects/postgresql/ext.py
+++ b/lib/sqlalchemy/dialects/postgresql/ext.py
@@ -164,15 +164,19 @@ class ExcludeConstraint(ColumnCollectionConstraint):
         :param \*elements:
 
           A sequence of two tuples of the form ``(column, operator)`` where
-          "column" is a SQL expression element or the name of a column as
-          string, most typically a :class:`_schema.Column` object,
-          and "operator" is a string containing the operator to use.
+          "column" is either a :class:`_schema.Column` object, or a SQL
+          expression element (e.g. ``func.int8range(table.from, table.to)``)
+          or the name of a column as string, and "operator" is a string
+          containing the operator to use (e.g. `"&&"` or `"="`).
+
           In order to specify a column name when a :class:`_schema.Column`
           object is not available, while ensuring
           that any necessary quoting rules take effect, an ad-hoc
           :class:`_schema.Column` or :func:`_expression.column`
-          object should be used. ``column`` may also be a string SQL
-          expression when passed as :func:`_expression.literal_column`
+          object should be used.
+          The ``column`` may also be a string SQL expression when
+          passed as :func:`_expression.literal_column` or
+          :func:`_expression.text`
 
         :param name:
           Optional, the in-database name of this constraint.
diff --git a/test/dialect/postgresql/test_compiler.py b/test/dialect/postgresql/test_compiler.py
index e8bead008f..2c0dbfccd7 100644
--- a/test/dialect/postgresql/test_compiler.py
+++ b/test/dialect/postgresql/test_compiler.py
@@ -64,6 +64,7 @@ from sqlalchemy.testing import fixtures
 from sqlalchemy.testing.assertions import assert_raises
 from sqlalchemy.testing.assertions import assert_raises_message
 from sqlalchemy.testing.assertions import AssertsCompiledSQL
+from sqlalchemy.testing.assertions import eq_
 from sqlalchemy.testing.assertions import eq_ignore_whitespace
 from sqlalchemy.testing.assertions import expect_warnings
 from sqlalchemy.testing.assertions import is_
@@ -1054,13 +1055,9 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             Column("valid_from_date", Date(), nullable=True),
             Column("valid_thru_date", Date(), nullable=True),
         )
+        sql_text = "daterange(valid_from_date, valid_thru_date, '[]')"
         cons = ExcludeConstraint(
-            (
-                literal_column(
-                    "daterange(valid_from_date, valid_thru_date, '[]')"
-                ),
-                "&&",
-            ),
+            (literal_column(sql_text), "&&"),
             where=column("valid_from_date") <= column("valid_thru_date"),
             name="ex_mytable_valid_date_range",
             deferrable=deferrable_value,
@@ -1068,6 +1065,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
         )
 
         table.append_constraint(cons)
+        eq_(cons.columns.keys(), [sql_text])
         expected = (
             "ALTER TABLE mytable ADD CONSTRAINT ex_mytable_valid_date_range "
             "EXCLUDE USING gist "
@@ -1164,6 +1162,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
         m = MetaData()
         cons = ExcludeConstraint((text("room::TEXT"), "="))
         Table("testtbl", m, Column("room", String), cons)
+        eq_(list(cons.columns), [])
         self.assert_compile(
             schema.AddConstraint(cons),
             "ALTER TABLE testtbl ADD EXCLUDE USING gist "
@@ -1243,6 +1242,20 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             dialect=postgresql.dialect(),
         )
 
+    def test_exclude_constraint_expression(self):
+        m = MetaData()
+        tbl = Table("foo", m, Column("x", Integer), Column("y", Integer))
+        cons = ExcludeConstraint((func.int8range(column("x"), tbl.c.y), "&&"))
+        tbl.append_constraint(cons)
+        # only the first col is considered. see #9233
+        eq_(cons.columns.keys(), ["x"])
+        self.assert_compile(
+            schema.AddConstraint(cons),
+            "ALTER TABLE foo ADD EXCLUDE USING gist "
+            "(int8range(x, y) WITH &&)",
+            dialect=postgresql.dialect(),
+        )
+
     def test_exclude_constraint_literal_binds(self):
         m = MetaData()
         tbl = Table("foo", m, Column("x", Integer), Column("y", Integer))
-- 
2.47.2