table qualfification in PG on conflict

[Mike Bayer]

diff --git a/test/dialect/postgresql/test_compiler.py b/test/dialect/postgresql/test_compiler.py
index 88110ba..7bc5c5c 100644
--- a/test/dialect/postgresql/test_compiler.py
+++ b/test/dialect/postgresql/test_compiler.py
@@ -1258,6 +1258,22 @@ class InsertOnConflictTest(fixtures.TestBase, AssertsCompiledSQL):
                             "WHERE name != %(name_1)s "
                             "AND description != %(description_2)s")

+    def test_do_update_add_whereclause_references_excluded(self):
+        i = insert(
+            self.table1, values=dict(name='foo'))
+        i = i.on_conflict_do_update(
+            constraint=self.excl_constr_anon,
+            set_=dict(name=i.excluded.name),
+            where=(
+                (self.table1.c.name != i.excluded.name))
+        )
+        self.assert_compile(i,
+                            'INSERT INTO mytable (name) VALUES '
+                            "(%(name)s) ON CONFLICT (name, description) "
+                            "WHERE mytable.description != %(description_1)s "
+                            'DO UPDATE SET name = excluded.name '
+                            "WHERE mytable.name != excluded.name")
+
     def test_quote_raw_string_col(self):
         t = table('t', column("FancyName"), column("other name"))

[Mike Bayer]

https://gerrit.sqlalchemy.org/198

[Mike Bayer]

Enable include_table for ON CONFLICT whereclauses

Fixed issue in new PG "on conflict" construct where columns including those of the "excluded" namespace would not be table-qualified in the WHERE clauses in the statement.

Change-Id: Idfefc93e7e7b0d84805e23d5436d822d606f6a0a Fixes: #3807

→ <<cset 800a18aff292>>

[Mike Bayer]

got this completely wrong. we cannot put the tablename in the ON CONFLICT clause. ridiculous. see #3846.

query here should be:

INSERT INTO mytable (name) VALUES (%(name)s) ON CONFLICT (name, description) WHERE description != %(description_1)s DO UPDATE SET name = excluded.name WHERE mytable.name != excluded.name

[Mike Bayer]

So just to sum up. Original wrong query:

INSERT INTO mytable (name) VALUES (%(name)s)
ON CONFLICT (name, description) 
WHERE description != %(description_1)s 
DO UPDATE SET name = excluded.name 
WHERE name != name

incorrect fix:

INSERT INTO mytable (name) VALUES (%(name)s) 
ON CONFLICT (name, description) 
WHERE mytable.description != %(description_1)s 
DO UPDATE SET name = excluded.name 
WHERE mytable.name != excluded.name

what I am guessing at the moment is the actual fix:

INSERT INTO mytable (name) VALUES (%(name)s) 
ON CONFLICT (name, description) 
WHERE description != %(description_1)s 
DO UPDATE SET name = excluded.name 
WHERE mytable.name != excluded.name

[Mike Bayer]

Put include_table=True for DO UPDATE..WHERE, but not ON CONFLICT

Fixed regression caused by the fix in 3807 (version 1.1.0) where we ensured that the tablename was qualified in the WHERE clause of the DO UPDATE portion of PostgreSQL's ON CONFLICT, however you cannot put the table name in the WHERE clause in the actual ON CONFLICT itself. This was an incorrect assumption, so that portion of the change in 3807 is rolled back.

Change-Id: I442d8629496a8e405b54711cfcf487761810ae8a Fixes: #3846 Fixes: #3807

→ <<cset 942c2429c033>>