Use full column->type processing for ON CONFLICT SET clause

Fixed bug in new "ON CONFLICT DO UPDATE" feature where the "set"
values for the UPDATE clause would not be subject to type-level
processing, as normally takes effect to handle both user-defined
type level conversions as well as dialect-required conversions, such
as those required for JSON datatypes.   Additionally, clarified that
the keys in the set_ dictionary should match the "key" of the column,
if distinct from the column name.  A warning is emitted
for remaining column names that don't match column keys; for
compatibility reasons, these are emitted as they were previously.

Fixes: #3888
Change-Id: I67a04c67aa5f65e6d29f27bf3ef2f8257088d073

1 files changed, 46 insertions, 10 deletions

diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index b436b934f..169b792f5 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -862,6 +862,7 @@ import re
 import datetime as dt
 
 
+from sqlalchemy.sql import elements
 from ... import sql, schema, exc, util
 from ...engine import default, reflection
 from ...sql import compiler, expression
@@ -1499,17 +1500,52 @@ class PGCompiler(compiler.SQLCompiler):
         target_text = self._on_conflict_target(on_conflict, **kw)
 
         action_set_ops = []
-        for k, v in clause.update_values_to_set:
-            key_text = (
-                self.preparer.quote(k)
-                if isinstance(k, util.string_types)
-                else self.process(k, use_schema=False)
-            )
-            value_text = self.process(
-                v,
-                use_schema=False
+
+        set_parameters = dict(clause.update_values_to_set)
+        # create a list of column assignment clauses as tuples
+        cols = self.statement.table.c
+        for c in cols:
+            col_key = c.key
+            if col_key in set_parameters:
+                value = set_parameters.pop(col_key)
+                if elements._is_literal(value):
+                    value = elements.BindParameter(
+                        None, value, type_=c.type
+                    )
+
+                else:
+                    if isinstance(value, elements.BindParameter) and \
+                            value.type._isnull:
+                        value = value._clone()
+                        value.type = c.type
+                value_text = self.process(value.self_group(), use_schema=False)
+
+                key_text = (
+                    self.preparer.quote(col_key)
+                )
+                action_set_ops.append('%s = %s' % (key_text, value_text))
+
+        # check for names that don't match columns
+        if set_parameters:
+            util.warn(
+                "Additional column names not matching "
+                "any column keys in table '%s': %s" % (
+                    self.statement.table.name,
+                    (", ".join("'%s'" % c for c in set_parameters))
+                )
             )
-            action_set_ops.append('%s = %s' % (key_text, value_text))
+            for k, v in set_parameters.items():
+                key_text = (
+                    self.preparer.quote(k)
+                    if isinstance(k, util.string_types)
+                    else self.process(k, use_schema=False)
+                )
+                value_text = self.process(
+                    elements._literal_as_binds(v),
+                    use_schema=False
+                )
+                action_set_ops.append('%s = %s' % (key_text, value_text))
+
         action_text = ', '.join(action_set_ops)
         if clause.update_whereclause is not None:
             action_text += ' WHERE %s' % \