Repository: kayak/pypika
Pull request #281 (2019-07-31 03:09:13)

Added ability to have fieldless ON CONFLICT DO NOTHING
While using the PostgreSQL query builder in PyPika recently, I discovered there was no way to perform a ON CONFLICT DO NOTHING, with PyPika only allowing for ON CONFLICT (abc.xyz) DO NOTHING queries. 

ON CONFLICT DO NOTHING is useful in PostgreSQL for when you do not know which column is going to conflict, however in the event that any column conflicts, DO NOTHING should be applied. 

There is no need to implement ON CONFLICT DO UPDATE functionality, as PostgreSQL requires that ON CONFLICT is given a field to perform a DO UPDATE


Patch:
diff --git a/pypika/dialects.py b/pypika/dialects.py
index 6bef4da..17fa9a6 100644
--- a/pypika/dialects.py
+++ b/pypika/dialects.py
@@ -180,22 +180,28 @@ class PostgreQueryBuilder(QueryBuilder):
             return Field(term, table=self._insert_table)
 
     def _on_conflict_sql(self, **kwargs):
-        if self._on_conflict_field is None:
-            return ''
-        elif not self._on_conflict_do_nothing and len(self._on_conflict_updates) == 0:
-            raise QueryException('No handler defined for on conflict')
+        if not self._on_conflict_do_nothing and len(self._on_conflict_updates) == 0:
+            if not self._on_conflict_field:
+                return ''
+            else:
+                raise QueryException('No handler defined for on conflict')
         else:
-            conflict_query = ' ON CONFLICT (' + self._on_conflict_field.get_sql(with_alias=True, **kwargs) + ')'
+            conflict_query = ' ON CONFLICT'
+            if self._on_conflict_field:
+                conflict_query += ' (' + self._on_conflict_field.get_sql(with_alias=True, **kwargs) + ')'
             if self._on_conflict_do_nothing:
                 conflict_query += ' DO NOTHING'
             elif len(self._on_conflict_updates) > 0:
-                conflict_query += ' DO UPDATE SET {updates}'.format(
-                    updates=','.join(
-                        '{field}={value}'.format(
-                            field=field.get_sql(**kwargs),
-                            value=value.get_sql(**kwargs)) for field, value in self._on_conflict_updates
+                if self._on_conflict_field:
+                    conflict_query += ' DO UPDATE SET {updates}'.format(
+                        updates=','.join(
+                            '{field}={value}'.format(
+                                field=field.get_sql(**kwargs),
+                                value=value.get_sql(**kwargs)) for field, value in self._on_conflict_updates
+                        )
                     )
-                )
+                else:
+                    raise QueryException('Can not have fieldless on conflict do update')
 
             return conflict_query
 


Test patch:
diff --git a/pypika/tests/test_inserts.py b/pypika/tests/test_inserts.py
index 574e309..1c327f0 100644
--- a/pypika/tests/test_inserts.py
+++ b/pypika/tests/test_inserts.py
@@ -160,6 +160,14 @@ class PostgresInsertIntoOnConflictTests(unittest.TestCase):
             query = PostgreSQLQuery.update(self.table_abc).set('foo', 'bar').on_conflict(
                   'id').do_update(['name'], ["m"])
 
+    def test_insert_on_fieldless_conflict_do_nothing(self):
+        query = PostgreSQLQuery.into(self.table_abc).insert(1).on_conflict(None).do_nothing()
+
+        self.assertEqual('INSERT INTO "abc" VALUES (1) ON CONFLICT DO NOTHING', str(query))
+
+    def test_insert_on_fieldless_conflict_do_update_field(self):
+        with self.assertRaises(QueryException):
+            query = str(PostgreSQLQuery.into(self.table_abc).insert(1, "m").on_conflict(None).do_update(self.table_abc.name, "m"))
 
 class PostgresInsertIntoReturningTests(unittest.TestCase):
     table_abc = Table('abc')