Repository: kayak/pypika
Pull request #444 (2020-06-02 10:20:49)

Support postgres do update with EXCLUDED keyword
Current `do_update` with postgres doesn't support EXCLUDED keyword.
Without specific value, use EXCLUDED option with `do_update`

fixed: #443

coveralls: 
[![Coverage Status](https://coveralls.io/builds/31182220/badge)](https://coveralls.io/builds/31182220)

Coverage remained the same at ?% when pulling **8bd7e9dd4b5da8a1a514379937ab5e56f07e1bf3 on lntuition:support_postgres_excluded** into **cb28b93e9c1159e490c3559e8467c7cfebb0ec5a on kayak:master**.


Patch:
diff --git a/pypika/__init__.py b/pypika/__init__.py
index 538b235..ea0b8fd 100644
--- a/pypika/__init__.py
+++ b/pypika/__init__.py
@@ -94,4 +94,4 @@ from pypika.utils import (
 
 __author__ = "Timothy Heys"
 __email__ = "theys@kayak.com"
-__version__ = "0.37.8"
+__version__ = "0.37.7"
diff --git a/pypika/dialects.py b/pypika/dialects.py
index 65ef870..bddb88f 100644
--- a/pypika/dialects.py
+++ b/pypika/dialects.py
@@ -360,7 +360,7 @@ class PostgreQueryBuilder(QueryBuilder):
         self._on_conflict_do_nothing = True
 
     @builder
-    def do_update(self, update_field: Union[str, Field], update_value: Any) -> "PostgreQueryBuilder":
+    def do_update(self, update_field: Union[str, Field], update_value: Optional[Any] = None) -> "PostgreQueryBuilder":
         if self._on_conflict_do_nothing:
             raise QueryException("Can not have two conflict handlers")
 
@@ -371,7 +371,10 @@ class PostgreQueryBuilder(QueryBuilder):
         else:
             raise QueryException("Unsupported update_field")
 
-        self._on_conflict_do_updates.append((field, ValueWrapper(update_value)))
+        if update_value:
+            self._on_conflict_do_updates.append((field, ValueWrapper(update_value)))
+        else:
+            self._on_conflict_do_updates.append((field, None))
 
     @builder
     def where(self, criterion: Criterion) -> "PostgreQueryBuilder":
@@ -436,15 +439,19 @@ class PostgreQueryBuilder(QueryBuilder):
         if self._on_conflict_do_nothing:
             return " DO NOTHING"
         elif len(self._on_conflict_do_updates) > 0:
-            action_sql = " DO UPDATE SET {updates}".format(
-                  updates=",".join(
-                        "{field}={value}".format(
-                              field=field.get_sql(**kwargs),
-                              value=value.get_sql(with_namespace=True, **kwargs),
-                        )
-                        for field, value in self._on_conflict_do_updates
-                  )
-            )
+            updates = []
+            for field, value in self._on_conflict_do_updates:
+                if value:
+                    updates.append("{field}={value}".format(
+                        field=field.get_sql(**kwargs),
+                        value=value.get_sql(with_namespace=True, **kwargs),
+                    ))
+                else:
+                    updates.append("{field}=EXCLUDED.{value}".format(
+                        field=field.get_sql(**kwargs),
+                        value=field.get_sql(**kwargs),
+                    ))
+            action_sql = " DO UPDATE SET {updates}".format(updates=",".join(updates))
 
             if self._on_conflict_do_update_wheres:
                 action_sql += " WHERE {where}".format(
diff --git a/pypika/terms.py b/pypika/terms.py
index 95def41..f16143e 100644
--- a/pypika/terms.py
+++ b/pypika/terms.py
@@ -974,9 +974,9 @@ class Case(Term):
 
     @property
     def is_aggregate(self) -> Optional[bool]:
-        # True if all criterions/cases are True or None. None all cases are None. Otherwise, False
+        # True if all cases are True or None. None all cases are None. Otherwise, False
         return resolve_is_aggregate(
-              [criterion.is_aggregate or term.is_aggregate for criterion, term in self._cases]
+              [term.is_aggregate for _, term in self._cases]
               + [self._else.is_aggregate if self._else else None]
         )
 
diff --git a/setup.cfg b/setup.cfg
index b08a2f2..48b88b9 100644
--- a/setup.cfg
+++ b/setup.cfg
@@ -1,5 +1,5 @@
 [bumpversion]
-current_version = 0.37.8
+current_version = 0.37.7
 commit = True
 tag = True
 


Test patch:
diff --git a/pypika/tests/test_aggregate.py b/pypika/tests/test_aggregate.py
index 72b1769..44a7112 100644
--- a/pypika/tests/test_aggregate.py
+++ b/pypika/tests/test_aggregate.py
@@ -53,15 +53,6 @@ class IsAggregateTests(unittest.TestCase):
         v = 1 / fn.Sum(Field("foo"))
         self.assertTrue(v.is_aggregate)
 
-    def test__agg_case_criterion_is_aggregate(self):
-        v = (
-            Case()
-            .when(fn.Sum(Field("foo")) > 666, 'More than 666')
-            .else_('Less than 666')
-        )
-
-        self.assertTrue(v.is_aggregate)
-
     def test__agg_case_is_aggregate(self):
         v = (
             Case()
@@ -106,7 +97,7 @@ class IsAggregateTests(unittest.TestCase):
 
         self.assertFalse(v.is_aggregate)
 
-    def test__case_with_single_aggregate_field_in_one_criterion_is_aggregate(self):
+    def test__case_with_single_aggregate_field_is_not_aggregate(self):
         v = (
             Case()
             .when(Field("foo") == 1, 1)
@@ -114,7 +105,12 @@ class IsAggregateTests(unittest.TestCase):
             .else_(3)
         )
 
-        self.assertTrue(v.is_aggregate)
+        self.assertFalse(v.is_aggregate)
+
+    def test__case_all_constants_is_aggregate_none(self):
+        v = Case().when(True, 1).when(False, 2).else_(3)
+
+        self.assertIsNone(v.is_aggregate)
 
     def test__non_aggregate_function_with_aggregated_arg(self):
         t = Table("abc")
diff --git a/pypika/tests/test_inserts.py b/pypika/tests/test_inserts.py
index 3f888c6..b7b07b2 100644
--- a/pypika/tests/test_inserts.py
+++ b/pypika/tests/test_inserts.py
@@ -519,6 +519,17 @@ class PostgresInsertIntoOnConflictTests(unittest.TestCase):
         self.assertEqual(
             'INSERT INTO "abc" VALUES (1,\'m\') ON CONFLICT ("id") DO UPDATE SET "abc"=1 WHERE "abc"."abc"=1', str(qs))
 
+    def test_on_conflict_do_update_with_excluded_where(self):
+        qs = (
+            PostgreSQLQuery.into(self.table_abc)
+                .insert(1, "m")
+                .on_conflict("id")
+                .do_update('abc')
+                .where(self.table_abc.abc.eq(1))
+        )
+        self.assertEqual(
+            'INSERT INTO "abc" VALUES (1,\'m\') ON CONFLICT ("id") DO UPDATE SET "abc"=EXCLUDED."abc" WHERE "abc"."abc"=1', str(qs))
+
     def test_on_conflict_where_complex(self):
         table_bcd = Table('bcd')